Duplicates are GOOD: How to find the most duplicated values?

[flanneryd]

In many of the existing posts I have reviewed here, the desire is to remove
duplicate values from a table.

I do not want to remove or eliminate duplicates. What I need help with is
finding which value occurs most often in a table of data - both across
columns and rows. Then which value is second-most duplicated, etc. The data
is text: one to four characters in length.

Example:
COL1 COL2 COL3 COL4 COL5
ROW1 aaaa bbbb aaaa cccc dddd
ROW2 abc abc bbb bbbb aaaa
ROW3 aaaa fgfg abc aaaa eeee

Result:
aaaa = most duplicated value (5 times)
abc = 2nd most duplicated (3 times)
bbbb = 3rd most duplicated (2 times)

Thanks for your help!

 

[T. Valko]

The *easiest* way to do this is to list the unique entries somewhere, say,
G2:G9. Then, enter this formula in H2 and copy down to H9 to get a count of
each entry:

=COUNTIF(A$1:E$3,G2)

Then, select G2:H9 and sort descending based on column H.

Biff