Check and Remove Records
 

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


--
Madasamy
------------------------------------------------------------------------
Madasamy's Profile: http://www.excelforum.com/member.php...o&userid=34146
View this thread: http://www.excelforum.com/showthread...hreadid=539170

Check and Remove Records
 

You can use advanced filter for this purpose.

Copy a key column, e.g. "Part Number" from the small list to the empty
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 copied
from the small data base


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=539170

Check and Remove Records
 

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.


"Madasamy" wrote:


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


--
Madasamy
------------------------------------------------------------------------
Madasamy's Profile: http://www.excelforum.com/member.php...o&userid=34146
View this thread: http://www.excelforum.com/showthread...hreadid=539170

Check and Remove Records
 

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.

Madasamy wrote:

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

--
Madasamy
------------------------------------------------------------------------
Madasamy's Profile: http://www.excelforum.com/member.php...o&userid=34146
View this thread: http://www.excelforum.com/showthread...hreadid=539170

--

Dave Peterson