how to formulate new columns for unique values and duplicates

[nickgoss]

Ive spent the last 2 hours almost searching and cant quite seem to find the right formula(s) to get what i want.

I have a list of employee numbers (that are occurring more than once) and want two things from that list, the first being a separate column displaying the employee numbers without duplicates and the second being the number of times that employee number has appeared in the list.

My end goal is something like this and i am a complete beginner with excel.

Original list - Employee Number - Occurrences

1234 1234 4
1234 5678 2
1234
5678
5678
1234

 

[tyija1995]

Excel has an inbuilt remove duplicates tool built under Data ribbon tab - so copy Column A to Column B, highlight Column B and Remove Duplicates -> Then to get Occurrences use a COUNTIF function

=COUNTIF($A$1:$A$6,B1)

For example will count how many times the value in cell B1 appears in the data range A1:A6 - change the range to see fit to your situation, put this formula in cell C1 (or whereever is best as you might have headers so then start with C2 and adjust accordingly)

 

[nickgoss]

Thank you for your quick reply! This has worked and now i feel stupid as it was so simple, been watching too many far more complex videos and giving myself a headache lol