Excel Spreadsheet question

[Guest]

I have two spreadsheets. On one I have 20,000 customers with customer ids
which are in numeric form like 19678. On the other spreadsheet I have 500
customers with customer ids. The second speadsheet is a list of customers I
need to delete from the larger 20,000 customer list. All of the customer ids
on the small list exist on the larger list.

I could search for each id that I want to delete individually but that would
be very time consumming. Is there a easy way to tell excel to look at the
smaller list and whenever it sees a match in the bigger list the match is
deleted?

Thanks in advance for your help.

 

[Bernard Liengme]

I will assume the IDs are column A in both sheets and that row 1 is either
empty or contains labels.
If not, insert a bank row 1
On the large sheet, insert a new column A
In A2 of the large sheet enter
=IF(ISERROR(VLOOKUP(B2,Sheet2!$A$2$A$501,1,FALSE)),0,1)
Copy down the column - easy way is to double click the fill handle (little
solid square in lower right corner of active cell)
Now you have 0's and 1's where 1 means the same ID is in both sheets
Select all of the data INCLUDING the top row and use Date | Filter | Auto
Filter
IN the drop down box in A1 chose 1
Now only the duplicate records are visible
Select the row number for these and do a Rows Delete
Turn off the filter; delete column A in the large sheet.
Done
best wishes

 

[Guest]

I will assume the IDs are column A in both sheets and that row 1 is either
empty or contains labels.
If not, insert a bank row 1
On the large sheet, insert a new column A
In A2 of the large sheet enter
=IF(ISERROR(VLOOKUP(B2,Sheet2!$A$2$A$501,1,FALSE)),0,1)
Copy down the column - easy way is to double click the fill handle (little
solid square in lower right corner of active cell)
Now you have 0's and 1's where 1 means the same ID is in both sheets
Select all of the data INCLUDING the top row and use Date | Filter | Auto
Filter
IN the drop down box in A1 chose 1
Now only the duplicate records are visible
Select the row number for these and do a Rows Delete
Turn off the filter; delete column A in the large sheet.
Done
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email




Thank You!

 

[Bernard Liengme]

Glad it worked for you.