SOLVED

How to remove values that show up more than once NOT just duplicates

Brass Contributor

This is what i have:

 

NamesDOBProduct
John Smith1/1/2020Car
John Smith1/1/2020House
Mary Jane2/2/2020Car
Nelson Mandella3/3/2020Car
Tom Trump4/4/2020House
Tom Trump4/4/2020Car
Katie Jacobs12/12/2019Car

 

This is what i want:

 

NamesDOBProduct
Mary Jane2/2/2020Car
Nelson Mandella3/3/2020Car
Katie Jacobs12/12/2019Car

 

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?

18 Replies

@shade206 

 

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.

I added a sample Excel sheet, can you provide the answer relative to the actual Sample? Sorry i'm a novice with Excel...

@shade206 

 

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

@Jack_Bumgarner 

 

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-205c6b0...

 

The above link is a good resource for explaining SPILL/Dynamic formulas. If the formula doesn't work let me know.

Thank you for your efforts. However, when i copy and past these formula into a cell of a empty column its says "Function is not valid"
Is there a solution for IF you do not have 2016 365 i.e. another way to do this?

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.

No, that is not the solution i'm looking for as all that does is count how many times a value is presented, not count only the values that have showed up only once.

any other ideas?

@shade206 

 

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.

Also, im using Office 365, so im not sure why your first formula didnt work

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

 

NamesDOBProduct  
John Smith1/1/2020Car 1
John Smith1/1/2020House 2
Mary Jane2/2/2020Car 2
Nelson Mandella3/3/2020Car 1
Tom Trump4/4/2020House 1
Tom Trump4/4/2020Car 2
Katie Jacobs12/12/2019Car 2

 

...but if i filter to "1" i still am showing a customer who showed up more than once, but just showing the once. 

best response confirmed by shade206 (Brass Contributor)
Solution

@shade206 

Couple of more variants as here

image.png

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.

@shade206

 

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

@shade206 

 

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

www.facebook.com/sysescola

 

 

https://techcommunity.microsoft.com/t5/excel/how-to-remove-values-that-show-up-more-than-once-not-ju...

 

@Gilberto_Moreira 

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.

@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.

@shade206 please have a look

1 best response

Accepted Solutions
best response confirmed by shade206 (Brass Contributor)
Solution

@shade206 

Couple of more variants as here

image.png

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.

View solution in original post