+ Reply to Thread
Results 1 to 6 of 6

Filter rows and delete duplicates

  1. #1
    Registered User
    Join Date
    04-07-2010
    Location
    Frankfurt, Germany
    MS-Off Ver
    Excel 2003
    Posts
    5

    Post Filter rows and delete duplicates

    Hi, i have this problem:

    i got several thousand data entries with several columns, lets say from A-C.
    In Column A i have an ID which often exists more than once because in column B-C are different comments / entries for that ID.

    Now i got a set of "filter" ID's which i want to use to delete every ROW, which has one of the "to be filtered" IDs in column A.

    Maybe if u look at my sample sheet you will understand what i mean:
    A-C is my data, i want to sort out every row which ID (F-Axxx) exists in column E (the filter criteria)
    How do i achieve that?

    If i use Special Filter it does exact the opposite what i need, it keeps those entries which are found in the category filter and deletes all the rest.

    I would be very glad if someone could help me.
    Thanks a lot in advance!
    Attached Files Attached Files
    Last edited by lunautic; 04-08-2010 at 02:10 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Filter rows and delete duplicates

    Use an added column to create a "key" for deletion. This would also be easier if the "search" list were on another sheet. Move that data to Sheet2 column A

    1) In D1 put "Key"

    2) In D2 put this formula, then copy down:

    =ISNUMBER(MATCH(A2,Tabelle2!A:A,0))

    ...a series of TRUE/FALSE answers will appear.

    3) In D1 turn on the Data > Filter > Autofilter

    4) Filter that column by "TRUE"

    5) Highlight all the visible data in A:C and Edit > Delete Row > ShiftUp

    6) Turn off the Autofilter and delete the data in column D


    Done.
    Last edited by JBeaucaire; 04-07-2010 at 02:51 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    04-07-2010
    Location
    Frankfurt, Germany
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Filter rows and delete duplicates

    thank you for your effort.
    sorry this is a bit emberrassing but i cant get it to work. I do exactly as you explained, but i get a "This formula contains an error" message and it points to the "Tabelle2!A:A" entry.

    Could you maybe send me your sheet how you did it?

    Thanks a bunch.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Filter rows and delete duplicates

    My formula uses commas because I'm in the US. I expect you'll need to use semicolons?

  5. #5
    Registered User
    Join Date
    04-07-2010
    Location
    Frankfurt, Germany
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Filter rows and delete duplicates

    oh gosh, yeah that was my mistake.

    Thanks you VERY much, this will save me a lot of time.
    + REP!

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Filter rows and delete duplicates

    Awesome.

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1