Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advanced Filtering (Excel 2003)

    If I have filtered a worksheet using a criteria list (perhaps some account numbers belonging to a certain group). The records in the resulting worksheet are the matched records; I now want to move these records/worksheet to a separate worksheet leaving behind those records that did not match. The left behind records/worksheet I now want to appear in contiguous rows with no gaps left from the records that were removed. So the end result is two spread sheets 1. Those matching my criteria and 2. All the rest.. I thought this would be fairly straightforward but I must have skipped class that day. I appreciate any help you folks can give me, I know there is an answer out there...somewhere..

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advanced Filtering (Excel 2003)

    After copying the filtered records, select the filtered records.
    Select Edit | Go To..., click Special, select Visible Cells Only, then click OK. (*)
    Select Edit | Delete Rows.
    Remove the filter.
    The remaining records should be contiguous.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Advanced Filtering (Excel 2003)

    Why not do a second advanced filter, setting the criteria to Not Equal ("<>") the criteria used for the first extraction.

    You could then either remove the original sheet or back it up as a safekeep.

    Andrew C

  4. #4
    New Lounger
    Join Date
    Feb 2001
    Location
    Georgia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Filtering (Excel 2003)

    I am happy to be a lounger, you guys are the greatest, this was the very answer i was seeking because it came up once before a long, time ago but the help files were of no use at all, even at the M$ website. This procedure did the trick and worked perfectly. Thanks HansV and all the other loungers who took the time to look/see. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> Thanks Andrew, your solution would work also if I had a formula, but it was a list in a separate spreadsheet.

Posting Permissions

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