Forum Discussion
How to remove values that show up more than once NOT just duplicates
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.
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
| Names | DOB | Product | ||
| John Smith | 1/1/2020 | Car | 1 | |
| John Smith | 1/1/2020 | House | 2 | |
| Mary Jane | 2/2/2020 | Car | 2 | |
| Nelson Mandella | 3/3/2020 | Car | 1 | |
| Tom Trump | 4/4/2020 | House | 1 | |
| Tom Trump | 4/4/2020 | Car | 2 | |
| Katie Jacobs | 12/12/2019 | Car | 2 |
...but if i filter to "1" i still am showing a customer who showed up more than once, but just showing the once.
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.