Hi,

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

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

any help would be really appriciated.

Thanks


--
Gopher_By_Fende
------------------------------------------------------------------------
Gopher_By_Fende's Profile: http://www.excelforum.com/member.php...o&userid=35318
View this thread: http://www.excelforum.com/showthread...hreadid=550944

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
--
Allllen


"Gopher_By_Fende" wrote:


Hi,

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

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

any help would be really appriciated.

Thanks


--
Gopher_By_Fende
------------------------------------------------------------------------
Gopher_By_Fende's Profile: http://www.excelforum.com/member.php...o&userid=35318
View this thread: http://www.excelforum.com/showthread...hreadid=550944

Cheers Allen, that worked like a charm!


--
Gopher_By_Fende
------------------------------------------------------------------------
Gopher_By_Fende's Profile: http://www.excelforum.com/member.php...o&userid=35318
View this thread: http://www.excelforum.com/showthread...hreadid=550944

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,
--
Allllen


"Gopher_By_Fende" wrote:


Cheers Allen, that worked like a charm!


--
Gopher_By_Fende
------------------------------------------------------------------------
Gopher_By_Fende's Profile: http://www.excelforum.com/member.php...o&userid=35318
View this thread: http://www.excelforum.com/showthread...hreadid=550944