Count each unique value and it's occurrences

[zudecke]

Hi guys,

I have a the following dilemma:

A1
tom
harry
charlie
jones
charlie
charlie
sam
tom
tom
sam


With the above in mind, do you know how I can count each unique value and the subsequent occurrences in a given column?

So, the output would be something like:
tom 3
sam 2
charlie 3
jones 1


Thanks!

Z

[TMS]

I'd use a Pivot Table. But there are formulae to extract unique entries from a list. Then you'd use COUNTIF to get the numbers.

See this thread for an example of extracting a list: https://www.excelforum.com/excel-for...p-10-list.html

The post from Glenn Kennedy has a sample workbook.

In your case, you could put this formula in cell D2 and copy it down:

Please Login or Register  to view this content.

Then, in E2, use:

Please Login or Register  to view this content.

[Czeslaw]

unique

Please Login or Register  to view this content.

count each

Please Login or Register  to view this content.

[TMS]

@Czeslaw: I think I said exactly that a little while back ...

[zudecke]

Hi both,

Thanks so much for your responses haha.

I don't get what column Ds involvement is in this though?

Thanks

Z

[Zer0Cool]

If your data isnt dynamic just copy your list to another range/sheet, use remove duplicates, then use the COUNTIF formula as others have given.

[zudecke]

I've coped the range to a new sheet but tis isn't working:

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


Nor am I sure how it should work? I don't know where the concept of column D came from? In this instance I just have 1 column (A)

[zudecke]

For example, I've attached a sheet with a random list of values.

Underneath, or wherever, I'd like each value listed, alongside the count of occurrences against each?

Sorry, I'm having a hard time explaining it

[Zer0Cool]

See attached.

Copied your list to other column, Data | remove duplicates, added COUNTIF formula, done. Took me about 30 seconds.

[TMS]

Put this in cell B2 and copy it down:

Please Login or Register  to view this content.

and this in C2 and copy down:

Please Login or Register  to view this content.

[Czeslaw]

@TMS:excuse me
Exact match.

[zudecke]

Thank you so much @TMS and everybody else - really a great help!

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[zudecke]

Done, thanks again! Life savers and a credit to the community and society in general!