-1

I have a excel file which needs to be unique, I want to remove the duplicates, all kinds of duplicates like below

enter image description here

Going to data->remove duplicates is not working. i found a code, which is also not working

Sub SimpleExample()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3) , Header:=xlYes
End Sub

Can anyone help me with this Thanks in advance

N S
  • 91
  • 7
  • Please post your data as a [markdown table](https://stackoverflow.com/editing-help#tables) rather than an image. A sample of the expected output would also be useful to clarify what you're trying to achieve. See [ask] for more details. – Spencer Barnes Jan 25 '23 at 08:54
  • This post has some useful answers for this ... https://stackoverflow.com/questions/72748553/excel-remove-duplicates-based-on-2-columns-case-sensitive?rq=1 – user10186832 Jan 25 '23 at 08:58
  • 5
    Just a FYI, but there is still human logic involved here. How do you expect Excel to know why to pick `United States` as the most correct version of what we as humans know as the USA? Same with any of the other values really. I think, maybe more feasible would be the top pick of all values that look similar instead. – JvdV Jan 25 '23 at 09:00
  • 3
    Can there be also entries like `3 Country Germany` or `4 Country United States` or `3 Fruit apple`? Are those duplicates or not? Can there be digits? I guess the first step is that you precisely define what a duplicate is – FunThomas Jan 25 '23 at 09:02
  • Hi, the proper value United States(the value we get if we use =PROPER) alone must be present. It can contain digits, only the value columns must be unique, it can have 3 country united states, 4 country germany. – N S Jan 25 '23 at 09:25
  • Please edit your question to provide a more comprehensive example, one that includes the multiple variations of what you might encounter, as well as the desired output from that example. **Provide the example as Text which can be copy/pasted**, perhaps using this perhaps using this [Markdown Tables Generator](https://www.tablesgenerator.com/markdown_tables). See [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors-when-asking-a-question) – Ron Rosenfeld Jan 25 '23 at 12:22

1 Answers1

1

The easiest way is to use Advance filter check this site

Excel Advanced Filter Data Tab Excel Advanced Filter Dialog Box

You can use VBA as well to manipulate Advance filter.

Add this function to a module

Function cleanVal(ByVal Val)
       Val = LCase(Trim(Val))
       Val = Replace(Val, "-", " ")
       Val = Replace(Val, ",", "")
       cleanVal = StrConv(Val, vbProperCase)
End Function

and in column next to table last column and cell value "= cleanVal(C3)" and find the duplicates on new column , that should cover most of the variation .

xShen
  • 452
  • 4
  • 9