Check and Remove Records

[Madasamy]

Dear All,

I have two spreadsheets one with 10,000 records and the other with 1000
records. I want to check the 10,000 records spreadsheet with the 1000
records and highlight the 1000 records present if any in the 10,000
records spreadsheet. (I want to check through the big list and remove
any records that are in the small list).

Thanks in advance

 

[CaptainQuattro]

You can use advanced filter for this purpose.

Copy a key column, e.g. "Part Number" from the small list to the empt
rows below the last record in the large list.

Select all the cells of the large database.

Select Data > Filter > Advanced Filter

Filter the list in place

Criteria range will be all the cells of the column(s) that you copie
from the small data bas

 

[Guest]

Use the VLOOKUP funtion. Do you know it? It is used to look at two sets of
data to see whether the data which you are looking at resides in the other
set of data.

People find VLOOKUP a bit tricky at first. If you can't get it working, let
me know.

 

[Dave Peterson]

Are the records in one cell or do you have a key column that can be used to
match up?


I'm gonna assume that column A is the only column or column A is the key column.

I'd insert a new column B
Add headers to row 1

In B2:
=isnumber(match(a2,sheet2!a:a,0))

Then drag down.

You'll see True if the key is in sheet2, column A.

You'll see False if it's not in sheet2, column A.

Then Apply Data|Filter|autofilter to column B
Use the drop down arrow to show the True's
delete those visible rows
data|filter|autofilter to remove the filter
delete column B.