I have a spread sheet with 2000 lines on it. In column A there are different reference numbers. I need to find all duplicate reference numbers.

How do I do this?

Wish was riding my bike 😐

Look under help for 'Remove duplicate rows' or 'Count unique values among duplicates'

and where the hell is Stoner? 🙄

i tend to run a pivot table which identifies all the unique numbers, it will also tell you the number of occurances of each. what are you trying to do?

I tried that geoffj, no luck.

I've just ordered the line numerically and looked through, deleting as I go. If anyone knows a less soul destroying method please post on here.

Living life on the edge 😥

=countif(range,"criteria")

EDIT: forget this it'll just tell you how many…sorry. Must read post…

Thanks 😀

waiting for a train. sorry

Lookup.

Try this. It will show True or False. Edit cells to fit yours

=COUNTIF(A:A,A1)>1

Drag down column & then sort

trying to do this on phone
which incidence of a given ref no. do you want to keep and which delete?

I'm not sure if there's a flash function but when I used to do this manually I used to:

1 – sort in ascending order
2 – add another column called 'duplicate'
3 – use an IF to set the duplicate cell to 1 if the number above it was the same (e.g. IF(A2=A1,1,0))
4 – copy and paste values in this column
5 – sort the whole sheet according to the duplicate column (make sure you're not just sorting the reference column but the whole data set)
6 – delete out all the duplicates and remove the extra column if necessary.

Shouldn't take more than 2 mins.

Hope that helps.

If you have already sorted them into ref number order you could add another column to identify duplicates. Have your records starting at row 2 with a blank row in row 1, refnos in column A, enter the formula =(A2=A1) into the first record's new column and copy down. This will return FALSE if the the ref no is different to the one in the record above, TRUE if it is the same.

Top work Chaps/Girls i've managed to bodge it now 🙂

Only 60 mins to go!

Reread your post. Obviously you don't need to do the delete part if you're not removing them.

Also work with a copy in the first instance!

Also work with a copy in the first instance!

I like to live dangerously 😆

Try running auto_close macros that shut the worbook without saving, now that's living dangerously.