Remove duplicates

[paradise2sr]

Hi,

I am using the below code(extracted from internet)-

Please Login or Register  to view this content.

I think that it works fine for stacking/flattening data of multiple columns in multiple worksheet.
Then I use the function

Please Login or Register  to view this content.

It then further gets sorted and alphabetically arranged in which there are duplicates but in different form.e.g

A Grade Sun Korce P Ltd (605955053)
A Grade Sun Korces [P] Ltd (605951053)
A.G. He Ind (P) Ltd, Bhairaine (803459699)
A.G. Hya Ind. (P) Ltd. (803459699)
B & B International Trading (B.& B. International Trading Pvt.Ltd,Phara)
Pace Maa Pack Ud 855656316 (855656316)
Subhkamna Store
Aakash Foods Products,(902844607)Kh (902844607)
Aakash Foods Products,Kise (902844607)
)

What I want is that I want to retain only one.So,I want automatically to get one out of two. Duplicates is to be identified on the basis of number which matches irrespective of spelling mistake or typo in different sheet.

How this can be done into a single function of vba or any other.

[Kaper]

I'd focus on this option

or any other.

A good tool for it could be Power Query.
At first I prepared a longer version (will give it below just for reference), but then the shorter:

Please Login or Register  to view this content.

Changed type step is important, as fuzzy logic will not work with non-text data.

You can experiment with Threshold value. If this parameter is ommitted, (or equal default 0.8) then two last entries are not recognized as one, so I used lower one: 0.5 and for sample data is a good one.

Both queries are used in attached file. And as said before, second (the one I started with) query for reference:

Please Login or Register  to view this content.

[Kaper]

PS. And if needed, the list of removed duplicates could be obtained with similar query:

Please Login or Register  to view this content.

[paradise2sr]

Thanx for the kind response. Actually ,I hesitate to use PQ as my data would be changing very frequently adding and deletion.And this result is linked to some other data.This kind of real life problem I am facing during data analysis.

I would be please to let you know.

[Kaper]

Just a personal opinion:
PQ is perfect for situation when data is "changing very frequently by adding and deletion"
You (0r macro) change(s) data. Then either manualy right click on the table produced by PQ, select refresh Query and ... here it is. Or, to avoid right click and manual refresh, one couuld use an event handler for Worksheet_Change event to call refreshing. If you would like to focus on fuzzy match just in macro, you can google for fuzzy match cartesian product but the algorithms here are rather long (in terms of code), and (what's worse) quite computing power/time hungry and not so easy to implement. While PQ does it all almost in no time with just a little bit of code.

[paradise2sr]

Hi,

Thanx once again for your kind respose.

Have you seen my another post at https://www.excelforum.com/excel-pro...lly-order.html

The result seems as per your post #2 attachment seems to be fine but not tested on large data which I am dealing.I will test and let you know accordingly what problems I am facing.You can let me know the stepwise step for getting the result.

Can you try in the sample workbook in the link stated.

[paradise2sr]

I have duly tried in my actual large data but couldn't get it.

I have a data in A2 as header and data in A3 & below.Pls let me know the required steps.Kindly note I have only one column and not two or three,so no append or merging could exists.Office 2021 using.

[Kaper]

Make sure your data is in Excel table.

[Kaper]

You may add an empty lina in row 1 of my file and copy your data into my file. And then just make sure the table (now starting in A2 extends down to end of your data

[paradise2sr]

I just pasted data in A2 but doesn't seems to work if I refresh H and K column.I think results are in H column and K column.
Plz elaborate in detail.

[Kaper]

Unfortunately it is not easy to diagnose the problem if you do not show the file. It does not have to be real one. But something following the layout and format.
Have you tried to right click on result table (green one) and choose "Refresh" from the pop-up context menu?