SOLVED

Contributor

# 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?

18 Replies

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

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.

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

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

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

Hello,

=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

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

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.

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

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"

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

Is there a solution for IF you do not have 2016 365 i.e. another way to do this?

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

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.

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

@Jack_Bumgarner @shade206 This should work for the purpose.

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

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?

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

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.

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

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

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

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.

best response confirmed by shade206 (Contributor)
Solution

# Re: 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.

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

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

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

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

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

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

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.

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

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