Forum Discussion

shade206's avatar
shade206
Brass Contributor
Apr 15, 2020
Solved

How to remove values that show up more than once NOT just duplicates

This is what i have:   Names DOB Product John Smith 1/1/2020 Car John Smith 1/1/2020 House Mary Jane 2/2/2020 Car Nelson Mandella 3/3/2020 Car Tom Trump 4/4/2020 House...
BI & Data Analysis
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 16, 2020

    shade206 

    Couple of more variants as here

    To simplify a bit we will ignore dynamic ranges.  Formulas could be

    In E2

    =IFNA(
   LOOKUP(2,
      1/((COUNTIF($E$1:E1,$A$2:$A$100)=0)*
         (COUNTIF($A$2:$A$100,$A$2:$A$100)=1)),
      $A$2:$A$100),
"")

    in F2

    =IFNA(INDEX(B$2:B$10,MATCH($E2,$A$2:$A$10,0)),"")

    in G2

    =IFNA(INDEX(C$2:C$10,MATCH($E2,$A$2:$A$10,0)),"")

    and drag all of them down till empty cells appear.

     

    If data shall be in same order as in source table, when in I2

    =IFERROR(
   INDEX(
      $A$2:$A$100,
      AGGREGATE(15,6,
         1/((COUNTIF($I$1:I1,$A$2:$A$100)=0)*
            (COUNTIF($A$2:$A$100,$A$2:$A$100)=1))*
            (ROW($A$2:$A$100)-ROW($A$1)),1
      )
   ),
"")

    Rest is similar.

Jack_Bumgarner's avatar
Jack_Bumgarner
Former Employee
Apr 16, 2020

shade206 

 

Correct, it should count the items. Then you should be able to filter out of the new column anything that is not 1. 

 

If the formula returns 1 then that person only shows up in the list once.

 

-Jack B.

shade206's avatar
shade206
Brass Contributor
Apr 16, 2020

No, so i'm not looking to remove the customers who show up more than once's second showing, i'm looking to isolate customers who show up only once.

please review my initial post for clarification and the example

 

The COUNTIF formula give me

 

NamesDOBProduct  
John Smith1/1/2020Car 1
John Smith1/1/2020House 2
Mary Jane2/2/2020Car 2
Nelson Mandella3/3/2020Car 1
Tom Trump4/4/2020House 1
Tom Trump4/4/2020Car 2
Katie Jacobs12/12/2019Car 2

 

...but if i filter to "1" i still am showing a customer who showed up more than once, but just showing the once. 

  • Jack_Bumgarner's avatar
    Jack_Bumgarner
    Former Employee
    Apr 16, 2020

    shade206

     

    I think I see the issue, you put your formula in cell E2 whereas the formula I gave should go in cell E1 to start. Check to make sure that in the formula is on the correct line. 

     

    =COUNTIF(A:A,A1)

     

    A1 should be changed to whatever cell in column A is on the same row as the formula. So if you put your formula in E2 for instance change A1 to A2

     

    Regards,

    -Jack

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 16, 2020

    shade206 

    Couple of more variants as here

    To simplify a bit we will ignore dynamic ranges.  Formulas could be

    In E2

    =IFNA(
   LOOKUP(2,
      1/((COUNTIF($E$1:E1,$A$2:$A$100)=0)*
         (COUNTIF($A$2:$A$100,$A$2:$A$100)=1)),
      $A$2:$A$100),
"")

    in F2

    =IFNA(INDEX(B$2:B$10,MATCH($E2,$A$2:$A$10,0)),"")

    in G2

    =IFNA(INDEX(C$2:C$10,MATCH($E2,$A$2:$A$10,0)),"")

    and drag all of them down till empty cells appear.

     

    If data shall be in same order as in source table, when in I2

    =IFERROR(
   INDEX(
      $A$2:$A$100,
      AGGREGATE(15,6,
         1/((COUNTIF($I$1:I1,$A$2:$A$100)=0)*
            (COUNTIF($A$2:$A$100,$A$2:$A$100)=1))*
            (ROW($A$2:$A$100)-ROW($A$1)),1
      )
   ),
"")

    Rest is similar.

Resources