CODEWITHSUNDEEP
excelexcel-formula
Marcelo
Marcelo

Reputation: 1196

Show all unique elements in a list and how many times they appeared

I am trying to make a formula that would return me a list of all unique elements in a list and how many times each appeared. I can't predict what is going to be added to the list, but it is a list of strings.

Example:

Car
Car
Bike
Car
Bus
Bus

Would result in:

Bike 1
Bus 2
Car 3

Thanks!

Upvotes: 1

Views: 82

Answers (2)

Brankica
Brankica

Reputation: 31

Or try Data - Consolidate enter image description here

Just insert number 1 in column right.

Than choose as in picture. enter image description here

When press ok you'll get

enter image description here

Upvotes: 1

zx8754
zx8754

Reputation: 56004

Create unique list, paste below formula to C2 and enter as array formula, CTRL+SHIFT+ENTER. Then drag down to copy.

=INDEX($A$2:$A$7, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$7), 0))

Then use COUNTIF, paste to D2, then drag down to copy:

=COUNTIF($A$2:$A$7,C2)

enter image description here

Upvotes: 1

Related Questions