Excel Spreadsheet question
 

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.

Excel Spreadsheet question
 

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

"JFSJ" wrote in message
...
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.

Excel Spreadsheet question
 

"Bernard Liengme" wrote:

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

"JFSJ" wrote in message
...
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.


Thank You!

Excel Spreadsheet question
 

Glad it worked for you.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JFSJ" wrote in message
...


"Bernard Liengme" wrote:

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

"JFSJ" wrote in message
...
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.


Thank You!