duplicate entries

[ajcrew]

what is the command to find out how many duplicate entries you have in 1 column, then also make excel list the names of those duplicate entries.

for example: say column a contains the following numbers:
219
457
219
345
789
10
345
345

I want column B to say that 219 and 345 were duplicated in column a, and also count how many times they have been duplicated, "2" for 219 and "3" for 345.

[Down Under]

Assuming 219 is the value in a1, then in cell b1 put in =countif(a:a,a1) and fill down. It will then look something like this:

219 2
457 1
219 2
345 3
789 1
10 1
345 3
345 3

[arlu1201]

A slight addition to Down Under's solution. If you want to first display a list of unique entries in column A and then their respective counts, you can copy column A to a blank column and select Data-> Remove Duplicates. Then use the formula given by Down Under in the column to the right of the unique entries.

For e.g. if you copy column A to column G and then remove duplicates, you will be left with 5 out of 8 entries given in your 1st post. Then use this formula in column H =COUNTIF(A:A,G1) and drag down.

[BarryTSL]

Attached is a sheet I have addapted from one I use myself, but should still work ok for what you need. Follow the instructions on the first page.