Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Filter to show duplicates including original (Excel XP)

    Hi,

    I am filtering an excel list for duplicate values in one column using Advanced filtering with =COUNTIF($A$2:A2,A2)>1 as criteria. The filter is working well, but I would like to include the original record too, so that I can compair the two and eliminate the one that does not contain data in another column.

    Is there a way to extract all duplicated records including the original; i.e. Record ID 2 is duplicated further down the column, so the filter must display the original record ID 2 and the duplicated record ID 2.

    Tx
    Regards,
    Rudi

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

    Re: Filter to show duplicates including original (Excel XP)

    Say that your list range extends to row 37. Change the criteria formula to

    =COUNTIF($A$2:$A$37,A2)>1

    In other words, the first argument of COUNTIF is now the entire first column of the list range.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter to show duplicates including original (Excel XP)

    Magic -- Thankyou!
    Regards,
    Rudi

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter to show duplicates including original (Excel XP)

    I am trying to use this and it is not working for me. Where do you put the formula. I am putting it in the Criteria range of the advanced filter but it is giving me an error - Reference not valid. What am I doing wrong?

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

    Re: Filter to show duplicates including original (Excel XP)

    In order to use a formula as criteria for advanced filter, you must use a blank or a dummy text as header for the criteria range, and place the formula below the header. In the Advanced Filter dialog, specify the range consisting of these two cells as criteria range. Don''t put the formula itself in the dialog.

    See attached example.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter to show duplicates including original (Excel XP)

    Thanks for the explanation.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter to show duplicates including original (Excel XP)

    Hi Linda

    See <post:=552,458>post 552,458</post:> for another example. The difference here is that you can use this method to filter for unique records too by adding criteria to filter for each column.
    Regards,
    Rudi

Posting Permissions

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