Apr 15 2020 03:01 PM - last edited on Nov 09 2023 11:09 AM by
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?
Apr 15 2020 03:56 PM
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.
Apr 15 2020 03:58 PM
Apr 15 2020 04:06 PM
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
Apr 15 2020 04:11 PM
Also, for the formula to work I think you need Excel 2016, Office 365, or anything newer for it to work.
The above link is a good resource for explaining SPILL/Dynamic formulas. If the formula doesn't work let me know.
Apr 16 2020 08:29 AM
Apr 16 2020 09:00 AM
Apr 16 2020 09:06 AM
Hello,
For older Excel files, one thing you can try instead is use the COUNTIF() in a separate empty column and then you can filter down manually.
Use the formula
=COUNTIF(A:A,A1)
in the empty column instead. Drag the formula down to the bottom of your data. Afterward you should be able to manually filter down to lines with a "1" and you should get the same result.
Let me know if this doesn't work.
Regards,
-Jack B.
Apr 16 2020 09:29 AM
@Jack_Bumgarner @shade206 This should work for the purpose.
Apr 16 2020 09:36 AM
Apr 16 2020 09:42 AM
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.
Apr 16 2020 09:50 AM
Apr 16 2020 09:53 AM - edited Apr 16 2020 09:56 AM
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.
Apr 16 2020 10:48 AM
SolutionCouple 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.
Apr 16 2020 10:56 AM
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
Apr 16 2020 04:19 PM - edited Apr 16 2020 04:22 PM
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
Apr 17 2020 01:43 AM
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.
Mar 16 2021 12:36 PM
Mar 23 2021 04:20 AM
@shade206 please have a look
Apr 16 2020 10:48 AM
SolutionCouple 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.