create a macro to search an excel spread for duplicates
 

I have a 55k line item spreadsheet and want to search thru it to find
duplicate seriel #'s and once found populate a new spreadsheet with only the
duplicates.

create a macro to search an excel spread for duplicates
 

Do you have MS Access?

Import the file into Access, then create & save a 2-column query that simply
retrieves the serial #s and COUNTS the serial #s. Call the query Freq

Now create a new query that uses both the imported table and the Freq query.
Join on serial #. Include ALL the columns from the table and the Count
column from the Freq query. Put a criteria in the Count column of 1. Run
the query

"jamalin" wrote:

I have a 55k line item spreadsheet and want to search thru it to find
duplicate seriel #'s and once found populate a new spreadsheet with only the
duplicates.

create a macro to search an excel spread for duplicates
 

THANKS!, I do have MS access but get confused as to how to create & save a
2-column query that simply retrieves the serial #s and COUNTS the serial #s.
This should be simple. Would it be too much to ask if you could call me and
walk me thru it? if so my work # is 262-636-5113.

"Duke Carey" wrote:

Do you have MS Access?

Import the file into Access, then create & save a 2-column query that simply
retrieves the serial #s and COUNTS the serial #s. Call the query Freq

Now create a new query that uses both the imported table and the Freq query.
Join on serial #. Include ALL the columns from the table and the Count
column from the Freq query. Put a criteria in the Count column of 1. Run
the query

"jamalin" wrote:

I have a 55k line item spreadsheet and want to search thru it to find
duplicate seriel #'s and once found populate a new spreadsheet with only the
duplicates.

create a macro to search an excel spread for duplicates
 

Oops

I entered the second formula in E2

Peter

"jamalin" wrote:

I have a 55k line item spreadsheet and want to search thru it to find
duplicate seriel #'s and once found populate a new spreadsheet with only the
duplicates.

create a macro to search an excel spread for duplicates
 

"jamalin" wrote:

I have a 55k line item spreadsheet and want to search thru it to find
duplicate seriel #'s and once found populate a new spreadsheet with only the
duplicates.

Jamalin

To list all the duplicates use this formula:

=IF(COUNTIF($A$1:$A$55,$A2)1,$A2,"")

to list unique duplicates use
=IF(AND(COUNTIF($A$1:$A$55,A2)1,COUNTIF($E$1:E1,$ A2)<1),$A2,"")

and copy down, then paste Special values into a new workbook

Regards
Peter