Removing records using anther spreadsheet

[Gopher_By_Fende]

Hi,

does anyone know if it is possible to remove records from on
spreadsheet based on the entries in another spreadsheet?

i'll try to explain. i have a spreadsheet taken from an door contro
system, with each row containing a card number and name. this file i
about 4500 rows long. i have another spreadsheet which has a list o
about 1000 records, again with card numbers. basically the second lis
is people who have left and need removing from the system. is there
way i could get excel to remove all records from file one if the car
number is in file 2?

any help would be really appriciated.

Thank

 

[Guest]

Hi GBF,

Work on copies of your files in case this goes wrong.

Use VLOOKUP formula in a new column on your master list to see which of the
records are also on the file with records to remove.

This will give you a new column where anything to be deleted has its proper
code number, and anything you want to keep will not be found, so Excel
returns #N/A.

Then use autofilter on your master list new column for anything that is not
equal to #N/A. Select all the lines and delete them. You are left with the
ones you want to keep.

Allen

 

[Gopher_By_Fende]

Cheers Allen, that worked like a charm!

 

[Guest]

Hey GBF,

It is great to know. But I am trying to score points!
Can you give my answer a green tick (answered!) so that I can get a point
for it?

thanks,