Find Partial Duplicate values in a column

leeandoona · Feb 25, 2019

I have an annoying problem I never seem to solve properly and today I've had another bash at it and I still can't figure out the best way forward. The problem is I have a very large list of data that contains columns that have partial duplicates that I need to identify, and then turn into 'unique' records. Unfortunately the duplicate values can occur randomly at the front, back of middle of the text string and can be either words or numbers or both! For example, here is a sample of what I've got to work with;

[TABLE="width: 406"]
<colgroup><col></colgroup><tbody>[TR]
[TD]SANTAS LITTLE HELPER (116cm)[/TD]
[/TR]
[TR]
[TD]SANTAS LITTLE HELPER (128cm)[/TD]
[/TR]
[TR]
[TD]SANTAS LITTLE HELPER (140cm)[/TD]
[/TR]
[TR]
[TD]PETTICOAT WHITE - ONE SIZE[/TD]
[/TR]
[TR]
[TD]PETTICOAT RED - ONE SIZE[/TD]
[/TR]
[TR]
[TD]PETTICOAT BLACK - ONE SIZE[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER (S)[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER (M)[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER (L)[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER (XL)[/TD]
[/TR]
[TR]
[TD]COLOUR CHANGING CRYSTAL BALL[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER (128cm)[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER (140cm)[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER (158cm)

and here's what it ought to look like when I've identified and removed the duplicates;

[TABLE="width: 406"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Description[/TD]
[/TR]
[TR]
[TD]SANTAS LITTLE HELPER[/TD]
[/TR]
[TR]
[TD]SANTAS LITTLE HELPER[/TD]
[/TR]
[TR]
[TD]SANTAS LITTLE HELPER[/TD]
[/TR]
[TR]
[TD]PETTICOAT - ONE SIZE[/TD]
[/TR]
[TR]
[TD]PETTICOAT - ONE SIZE[/TD]
[/TR]
[TR]
[TD]PETTICOAT - ONE SIZE[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER[/TD]
[/TR]
[TR]
[TD]COLOUR CHANGING CRYSTAL BALL[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER[/TD]
[/TR]
[TR]
[TD]DEATHLY GRIM REAPER
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, this makes a lookup unlikely to work well and fuzzylookup isn't really working properly either. I need to essentially identify the duplicates and remove the differentials.

Is there any VBA that will solve this, or even get close would help? Thanks v much

Akuini · Feb 25, 2019

You can use “Text to Columns” in the menu and use “(“ as delimiter.

https://support.office.com/en-us/ar...s-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

PaddyD · Feb 25, 2019

It's likely that you've never solved it 'properly' because the problem is hard, especially when you're trying to solve a text parsing problem in a bit of kit that's primarily designed to deal with numbers (esp as far as the main functions are concerned).

To make meaningful progress, esp if the variation in the text strings is hard to completely define, you'll need to branch out into vba. You might find the UDFs here helpful:

https://www.mrexcel.com/forum/excel...zy-matching-new-version-plus-explanation.html

sheetspread · Feb 25, 2019

With your sample, find (*) replace with nothing followed by find * - replace with - returned the desired format. Can a similar method be applied to the rest?

leeandoona · Feb 26, 2019

Akuini said:
You can use “Text to Columns” in the menu and use “(“ as delimiter.

https://support.office.com/en-us/ar...s-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

This is no good I'm afraid. I'm attempting something much more complicated. Not all the data in the cells contains variants in brackets (). Some of it, as per the example, contains just words that are variations of the dup. e.g. White, Red, Black. Similarly, some of the data contains more than one instance of brackets;

some text (stuff) some text (more stuff) some text.

Not every cell contains data that has the same amount of words or characters. So a text to columns only really works on those cells that contain data that us very uniform in pattern. Mu data is approximately 25,000 rows deep or more. I'm needing something a bit more like a loop match lookup for the first part then a substitute/replace for the second part.

Thanks though, probably I should have made this clearer.

leeandoona · Feb 26, 2019

PaddyD said:
It's likely that you've never solved it 'properly' because the problem is hard, especially when you're trying to solve a text parsing problem in a bit of kit that's primarily designed to deal with numbers (esp as far as the main functions are concerned).

To make meaningful progress, esp if the variation in the text strings is hard to completely define, you'll need to branch out into vba. You might find the UDFs here helpful:

https://www.mrexcel.com/forum/excel...zy-matching-new-version-plus-explanation.html

I've been reading through the posts on that one actually. Some of it might work, kind of. The problem as you say, is my dataset is very hard to just apply some basic rules to. I've been experimenting with loops in vba and have a hefty manual on vba basics which I've trawled through but I think my skills are not up to the job of thinking it through like a vba expert would. I'm okay with some simple vba and have managed in the past to adapt some nicely written code by far better people to do some stuff I needed after a bit of head scratching. This though always foxes me. So far my work around takes many hours of faffing about running match, substitute and switch if statements end even then I still end up with many thousands of rows that i have to manually eyeball and fix. All a right pain in the proverbial. I was thinking maybe a loop match that looks up likely matched words and identifies the cells using conditional formatting so at least I can sort them and edit them..but even that seems to miss obvious 'matches'. Thanks for that link, I'll read a bit deeper and see if there is anything there I can make work.

leeandoona · Feb 26, 2019

sheetspread said:
With your sample, find (*) replace with nothing followed by find * - replace with - returned the desired format. Can a similar method be applied to the rest?

This is something I experimented with on the data that contain brackets (). Unfortunately some of the data contains more than 1 set of brackets;

This product is an example (Size 12) Red (adults)
This product is an example (Size 10) Red (adults)
This product is an example (Size 8) Blue (childs)

The data I work with also contains no strict patterns or uniform layout to the text, so somethings there will be brackets, other times a dash '-' or an oblique '/' and sometimes just a plain old space that separates the raw data from its variant. I've tried all manner of sorting, text to columns, find replace, substitutes and even some loop match in vba but everything I try always misses thousands of potential variant data that i then have to manually check. All very frustrating.

Akuini · Feb 26, 2019

I'm attempting something much more complicated.

I suggest you get help from another forum that has Word Help section, such as:
http://www.vbaexpress.com/forum/forumdisplay.php?20-Word-Help
because actually Word is better than Excel in dealing with words & character, so I believe you get better chance to get a solution.

That’s being said, if your data is not sensitive, you could upload your workbook (without sensitive data) somewhere (maybe to dropbox.com or google drive)? Then put the link here. Maybe someone here can find a solution.

I'm needing something a bit more like a loop match lookup for the first part then a substitute/replace for the second part.

Actually how do you manually determine the first part? Is it if some words (maybe 1,2 ,3 or 4 words) in the beginning of some cells are the same then those are the first part & considered as duplicate?

sheetspread · Feb 26, 2019

The fuzzyvlookup UDF that PaddyD suggested will work great if you have a master list to refer to. Otherwise I'm not sure it can filter each cell by common strings with a group.

sheetspread · Feb 26, 2019

It can also change each term to the first in the group, but you seem to only want common strings.

Find Partial Duplicate values in a column

leeandoona

New Member

Excel Facts

Akuini

Well-known Member

PaddyD

MrExcel MVP

sheetspread

Well-known Member

leeandoona

New Member

leeandoona

New Member

leeandoona

New Member

Akuini

Well-known Member

sheetspread

Well-known Member

sheetspread

Well-known Member

Similar threads

Forum statistics

Share this page

Find Partial Duplicate values in a column

New Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

New Member

New Member

New Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock