Excel Question (Macro, Vlookup, other method?): Same name highlighting

[max347]

Hi guys, I have a column of names in A column, and a column of names in B column. I need to highlight (or put into a third column) the duplicates. The catch- one column is in "first last" format and the other is in "last, first" format.

Ideas?

Thanks!

 

[Zorander]

Assuming clean formatting of the names, can you break up one of the columns using Text-to-Column, then recombine them to match the other column's format (using Concatenate or simply using "&" functions)?

Once you have the 2 columns matching in format, you can use the "if" function in column C to highlight any matching rows, e.g. IF(A2=B2,"DUPLICATE","").

 

[max347]

Unfortunately the one column is severely non-formatted. ie, different spacing, some have jr, sr, ms, etc.

I will check out this method and see what I can come up with however.

I was checking out the fuzzy sort thing by microsoft however it just seems like a way to do database style tie-togethers...not really a sort.

 

[Jaydip]

Macro is the way to go here.

 

[max347]

Yeah so this is what I was trying to use, but unfortunately it won't work with "similar" names, just exact matches-

Sub ColorDuplicates()
'Color duplicate items between columns A and B
    For i = 1 To Range("B65536").End(xlUp).Row
         If Application.WorksheetFunction.CountIf(Range("A:A"), Range("B" & i)) = 1 Then
            With Range("B" & i).Font
                .ColorIndex = 3
                .Bold = True
            End With
         End If
    Next i
    For i = 1 To Range("A65536").End(xlUp).Row
         If Application.WorksheetFunction.CountIf(Range("B:B"), Range("A" & i)) = 1 Then
            With Range("A" & i).Font
                .ColorIndex = 3
                .Bold = True
            End With
         End If
    Next i
End Sub

 

[Jaydip]

Have you tried regular expressions?

 

[obidamnkenobi]

Could you use InStr?
http://msdn.microsoft.com/en-us/library/8460tsh1(v=vs.80).aspx
I've used that before to search for X in a range. If instr("name")>1 then ... etc.

 

[max347]

Have you tried regular expressions?

Can you expound on this?

 

[max347]

Could you use InStr?
http://msdn.microsoft.com/en-us/library/8460tsh1(v=vs.80).aspx
I've used that before to search for X in a range. If instr("name")>1 then ... etc.

Well, this looks like it would work if one column was composed of half of the name, but I can't see how to use it with both columns having the same name but in different string format?

 

[Jaydip]

Can you expound on this?

You can use regular expression in VBA.These kind of tasks are well suited for RE.Do you want me to provide examples regarding that?