Forum Discussion
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 |
| Tom Trump | 4/4/2020 | Car |
| Katie Jacobs | 12/12/2019 | Car |
This is what i want:
| Names | DOB | Product |
| Mary Jane | 2/2/2020 | Car |
| Nelson Mandella | 3/3/2020 | Car |
| Katie Jacobs | 12/12/2019 | Car |
Basically i want to remove any row with the Name that shows up more than once, not just remove duplicates, but i want to know who all the customers are who only have one product regardless of what that product may be.
A little help?
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.
18 Replies
Uma opção é utilizando o PowerQuery, mas como você disse que é novato no excel, acredito que seja uma função avançada para a sua utilização neste momento.
One option is using PowerQuery, but as you said you are new to excel, I believe it is an advanced function for your use at this time.
Gilberto Moreira
SysEscola - Sistema de Gestão Escolar
Cansanção - Bahia - Brasil
http://www.facebook.com/sysescola
Power Query - it depends. Require refresh. Headache with adding columns to resulting table, especially with calculates. It has pros and cons. IMHO, in many cases if something relatively simply could be done by formulas, it's better to use formulas.
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
