Advanced Filter? or code to pull a subset

Topic

New Reply

I have been given a workbook with two worksheets. One sheet contains data in all 65,536 rows (sheetA).
The other sheet has 4,048 rows of data (sheetB). Both worksheets are formatted the same and have the same column headings. Sheet A contains all the sheetB records as well as other records.

I would like to filter file A using File B, thereby ending up with a list of records showing what is in Sheet A that is not in Sheet B

I tried using the advanced filter, but apparently I’m not doing it right. Maybe a macro would be better.

I thought about combining the lists and filtering for unique values, but since sheetA already uses all 65,536 rows, then I can’t add more to it.

Any thoughts?

Reply | Quote

Replies

On which column or columns do you want to match?

Reply | Quote

column A

Reply | Quote

Try this:

– Create a copy of Sheet A.
– Click anywhere in the data on the copy of Sheet A.
– Select Data | Filter | Advanced Filter…
– Click in the Criteria box.
– Activate Sheet B.
– Select the data in column A.
– Click OK.
– You will now have filtered the matching data on the copy of Sheet A.
– Select everything except the column headers in row 1.
– Press Delete.
– You have now deleted the matching data.
– Select Data | Filter | Show All Data.
– You’ll see only the non-matching data.
– Select the entire sheet and sort on column A.

Reply | Quote

That did work, Hans! Thank you. I was trying the advanced filter, but I couldn’t get it to work. I think I was doing it backwards. It worked with your instructions.

Reply | Quote