Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

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

- Home
- :
- Microsoft Excel
- :
- Excel
- :
- How to remove values that show up more than once NOT just duplicates

Discussion Options

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Apr 15 2020 03:01 PM - edited Apr 15 2020 03:58 PM

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?

Labels:

18 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Apr 15 2020 03:58 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Apr 16 2020 08:29 AM

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"

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Apr 16 2020 09:00 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Apr 16 2020 09:29 AM

@Jack_Bumgarner @shade206 This should work for the purpose.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Apr 16 2020 09:36 AM

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?

any other ideas?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Apr 16 2020 09:50 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

best response confirmed by
shade206* (Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 16 2021 12:36 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Mar 23 2021 04:20 AM

@shade206 please have a look