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.
Hello,
You can use a mixture of "COUNTIF" and "FILTER".
=FILTER(A1:C8,COUNTIF(A:A,A1:A8)=1,"")
Assuming you have your data start in column A, the above should work. Just don't put the formula in columns A through C. Column A should be the column of people whom you want to filter out. This formula will create a spill array of all the values so you can copy and paste to wherever you need. The above formula works because the count if function returns an array that counts the number of times a person shows up and filters the data down to when it only shows up one time.
In the above formula you will need to also update 8 to be the last row of the table data. Otherwise if you want to get really fancy you could incorporate INDIRECT and COUNTA into the mix and never have to update the formula again.
=FILTER(INDIRECT("A1:C" & COUNTA(A:A)),COUNTIF(A:A,INDIRECT("A1:A" & COUNTA(A:A)))=1,"")
I hope this helps, Let me know if you have more questions.
Regards
-Jack B.
