Mass find options

[Ian_A85]

Hello All,

I have a sheet of about 4500 entries, and on a second sheet of about 2000 entries, is there a way to a mass search to find any duplicates?

As at the moment I am doing line by line Find (Ctrl F) and its getting boring!!

there are muliple columns that can be search, ie invoice number (in A) or post code (in G)

 

[SteveO59L]

Try something along the lines of

=COUNTIF($A$1:A1,A1)

or

=COUNTIF(A:A,A1)


duplicates will return a number > 1

 

[Ian_A85]

Thanks Steve, can you explain this for me, as reading the statement, you'd have to do each cell in A, for example i'd have to run that for all 4500 lines? or am i reading it wrong?

Sorry for sounding dence!

 

[mancubus]

if you just want to see the duplicates, try conditional format.

xl2003: format - conditional formatting - "formula is" (for column A) "=COUNTIF(A:A,A1)>1"

xl2007: home tab - conditional formatting - new rule - format only unique or duplicate values - duplicate

 

[SteveO59L]

I was assuming that in a1:a4500, you have list a list of invoice numbers, some of which may be duplicated.

If you insert a column to the right of A, populate the first cell of this column with the required formula, and fill down to row 4500, it will return a value > 1 for each duplicated entry.

If this isnt the case, post a sample of your data, and what you want to do with it.

 

[Ian_A85]

Thats great guys, it makes sense now, and its working for me!