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.
- Jack_Bumgarner I was able to use the filter formula to filter my data into the area to the right of the main data. Now when I click on any of the filtered data I just get the filter formula. Is there a way to apply the filter so I can then manipulate the filtered info? I appreciate your help.
- I added a sample Excel sheet, can you provide the answer relative to the actual Sample? Sorry i'm a novice with Excel...
Hello,
Please use
=FILTER(INDIRECT("A1:C" & COUNTA(A:A)),COUNTIF(A:A,INDIRECT("A1:A" & COUNTA(A:A)))=1,"")
This formula should work as is. Just make sure you put the formula in a different column than the ones with the data. I would attach the excel file back to you but I do not see a button to add it back unfortunately.
Please let me know if you have any other questions.
Regards,
-Jack
Also, for the formula to work I think you need Excel 2016, Office 365, or anything newer for it to work.
https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531?ui=en-us&rs=en-us&ad=us
The above link is a good resource for explaining SPILL/Dynamic formulas. If the formula doesn't work let me know.
