Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    On which column or columns do you want to match?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts
    column A

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •