Excel: Single list into a table

Select a blank cell such as D2, and enter formula

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1,$B$2:$B$9),0)), "") 

into the Formula Bar, and then press the Ctrl + Shift + Enter keys simultaneously. (B2:B9 is the column data which you want to extract the unique values, D1 is the above cell where your formula is located)

The formula now should look like this:

{=IFERROR(INDEX($B$2:$B$9, MATCH(0, COUNTIF($D$1:D1, $B$2:$B$9), 0)), "")}

Keep selecting cell D2, then drag the Fill Handle down to get all unique values from the specified range.

Now all unique values in column range B2:B9 are extracted. When values in this range changed, the unique value list will be dynamically changed immediately.

After this point, you can use COUNTIF formula in an extra column in order to calculate the repetitions of each value. For example, enter the following formula in cell E2 and drag the Fill Handle down:

=COUNTIF($B$2:$B$9,D2) 

If you want to count the Occurrence as you have shown in 2nd Picture, you can use Countif function.

If you are looking for Unique entries in particular Data range, better use Data Validation.

let me say how, first select the Data range, then click Data validation command from Menu, then go to Settings and pick the Customs.

In the Formula Text Box just write this formula.

=COUNTIF($A$2:$A$20,A2)=1

This Formula will only allow each entry once within the said Data range.

If you feel you can set Input Message & Error Alert also.

NB: In my Formula the Data range is an example you change it as you need.