Finding duplication in name list

Excel 2010 does have a function to remove duplicates. But that only works if the text is exactly the same.

However, sometimes there some whose names are placed differently (Different placement of Middle and First names).
For example, "Jason Leong Zhi Kai & Leong Zhi Kai Jason".

Is there any way to find identify those that are duplicate despite having their middle/first name placed in different orders?

Any help is greatly appreciated!

Hi Bosetan,

You need to come out with some logic or process the data as per data pattern.
other wise, how can you tell Excel that which part of name is first name and which one is last name




Regards,
DILIPandey
<click on below * if this helps>

This is a workaround and I´m sure there would be better ways to do it with VB but this will probably get you in the right direction.

First you would have to get the individual sections of the names into separate cells. Using the Text to columns - delimited by space.

Then you could sort each line horizontally starting with the column B

Then you would sort vertically, starting with name 1, each consecutive column being an extra level.

Then you could apply a If statement on another column o check if all names in the row are the same as the one bellow.
If they are, you have a duplicate.

The attached sheet does that with a macro.

Paste your list of names Starting in A2 and then click the button. See how it words.
It is limited to 7 names per cell, delimited by space only, if statement down to 5000 rows

Make sure to enable macros when opening the sheet.

Originally Posted by Portuga

This is a workaround and I´m sure there would be better ways to do it with VB but this will probably get you in the right direction.

First you would have to get the individual sections of the names into separate cells. Using the Text to columns - delimited by space.

Then you could sort each line horizontally starting with the column B

Then you would sort vertically, starting with name 1, each consecutive column being an extra level.

Then you could apply a If statement on another column o check if all names in the row are the same as the one bellow.
If they are, you have a duplicate.

The attached sheet does that with a macro.

Paste your list of names Starting in A2 and then click the button. See how it words.
It is limited to 7 names per cell, delimited by space only, if statement down to 5000 rows

Make sure to enable macros when opening the sheet.

Hey man,

The file you attached is great. But its kinda complex for me to understand those formulas that you key in. (for example, setting up the macro)
Is it possible that list down the steps you took? Or link me a webby where there is a guide on this.

P/S: I've gotta redo this on my office comp as they're really strict with the security. Hence, I need to know the steps to make this work out well.

Thanks in advance man!

Edit: I've roughly grasped the concept of it but did encounter some issues on the way. When I tried sorting out the data as a whole (many rows at one time), the sorting doesn't seem to work. But if I were to sort it out individually (Row by row), it works perfectly. Any idea what's causing this issue and is there any solution to it? Lastly, I just need some guide on setting up the macros so that I can have my job done at the click of a button! Once again, thanks a lot for the help!

You have to do it row by row, that is why macros are great for repetitive tasks.

Press Alt+F11 this opens the VBA editor
Insert a module if there is none there

Paste the following code in the module:

Sub TextToColumnsandSortRows()

'First part of the macro: get the individual sections of the names into
'separate cells using the Text to columns - delimited by space. Recorded
' the macro  + made a couple edits

 ' Part 1 -if you want to keep the original cell with the whole name
 ' make sure you select the destination cell in the column after the
 'cell with the name. In this case the first cell with a name is A2,
 ' the destination range is B2.
    
     
     Range("A2:A5000").Select
    Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
        True
   '-----------------------------------------------------------------------------------------

 ' Part 2 - sort each row horizontally, one by one. Found this code online.
  'Make the r = 1 whatever the first row of data you want to sort on is.
'The Cells(r, 2) means your data starts in Col 2 or Col B
   
    
Dim r As Long
Dim lRow As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

For r = 1 To lRow
With Cells(r, 2).Resize(1, 7)
.Sort Key1:=Cells(r, 2), Order1:=xlAscending, Header:=xlGuess, _
Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
End With
Next r
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
'-----------------------------------------------------------------------------------------
       
'Third Part is to sort each column vertically, starting with name 1, each
' consecutive column being an extra level. This was recorded + made a few edits to suit.
'Name your column titles and aply a filter to them (A1:I1)
'Start to Record macro, select your range and then use the custom sort on Columns B to H  - Sort A to Z

   Range("A1:H5000").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B5000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C5000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D5000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E2:E5000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F2:F5000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("G2:G5000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H2:H5000") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:H5000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

In column I create the if statement
Then insert a basic shape. Any shape, and assign the macro TextToColumnsandSortRows to it

Hi bosetan

It is unnecessary to use "reply with quote" when replying here, unless there is a specific point in a poster's post you want to high-light - otherwaise all it does is take up space and cause clutter

Thanks

If you read the formula aloud, it basically says what it does:
If all separated cell names in one row match all the ones immediately bellow, then you have a duplicate, if not, not a duplicate.

See these on if statements:

http://www.experiglot.com/2006/12/11...th-and-or-not/

http://www.myonlinetraininghub.com/e...ions-explained

Originally Posted by FDibbins

It is unnecessary to use "reply with quote" when replying here, unless there is a specific point

This is a very good point.

No problem.

What you are asking now is a different problem and you should start a new thread for it in the Excel Programming / VBA / Macros section.

If you are happy with this solution, mark the thread as solved