Count occurrences of values

Question

I have a column of text values with repeated values. I want to create a new column of unique values (no repeats) and a column with the frequency of each of those values.

What's the easiest way to do that? Efficiency isn't much of a concern as it's under 10,000 rows.

brettdj - Have any links to related topics? I didn't see any when I searched. — sagacious, Oct 31 '12 at 05:23
One of the discussions: http://superuser.com/questions/49614/how-do-i-get-the-distinct-values-in-a-column-in-excel — Jüri Ruut, Oct 31 '12 at 07:29

pnuts · Answer 1 · 2015-12-14T13:54:07.830

23

For the sake of an answer rather than comments, copy column (say A) to B (say) and for B only Data > Data Tools – Remove Duplicates then in C2 enter:

=COUNTIF(A:A,B2)

and copy down as required

OR (preferred option)

use a PivotTable as @Tim:

SO13151320 example

edited Dec 14 '15 at 13:54

answered Mar 11 '13 at 16:03

pnuts

58,317
11
87
139

1

Clever and simple to do. – NoChance Jan 14 '14 at 14:46
doesn't work -> "the formula you typed contains an error" – user3182532 Sep 13 '17 at 13:13

Count occurrences of values

1 Answers1

Linked