Partial match in two columns and delete duplicate portion

[wittonlin]

I'm trying to add a snippet of code to a macro. I can find several vlookup functions, but nothing in a macro.

Here's a simple example.

Column A contains First name and column B contains last name, normally.

Sometime Column A contains First name AND Last name, while column B still contains Last name.

I need to search each unknown number of rows in column B and look for a match in column A. If there's a match, remove that portion from column A.

Col A Col B
Tom Jones Jones

changed to

Col A Col B
Tom Jones

Thanks to anyone able help!

 

[gallen]

Would it work by finding if there is 2 words (names) in column A and deleting anything after the space?

 

[S_Wish]

if there is no variant of Jones Tom then:

for i = 1 to range("A"&rows.count).end(xlup).row
if instr(range("A"&i),range("B"&i))>0 then

range("A"&i) = left(range("A"&i),instr(range("A"&i),range("B"&i))-2)
endif
next i

also if space is a separator in abovementioned example, we can take left from space

for i = 1 to range("A"&rows.count).end(xlup).row
if instr(range("A"&i)," ")>0 then

range("A"&i) = left(range("A"&i),instr(range("A"&i)," "))
endif
next i

 

[gallen]

If it is space delimited:

Sub Test()

    Dim s() As String
    Dim lr As Long, l As Long
    
    'last used row in col A
    lr = Range("A" & Rows.Count).End(xlUp).Row
    
    'loop all vlaues in col A
    For l = 2 To lr
        'split any value in col A
        s = Split(Range("A" & l), " ")
        'Set value to first split item
        Range("A" & l) = s(0)
    Next l
End Sub

 

[wittonlin]

Very cool!

The first one is the only one that works because at times there are Suffixes and Prefixes to the name.

It works perfect but I'm getting an rt 5 invalid procedure call or argument after it removes the name at...

Range("A" & i) = Left(Range("A" & i), InStr(Range("A" & i), Range("B" & i)) - 2)

I'm not sure why.

When done I'm going to try and reverse it because at times, for some weird reason, we get both first name and last name in both column A and B!

if there is no variant of Jones Tom then:

for i = 1 to range("A"&rows.count).end(xlup).row
if instr(range("A"&i),range("B"&i))>0 then

range("A"&i) = left(range("A"&i),instr(range("A"&i),range("B"&i))-2)
endif
next i

also if space is a separator in abovementioned example, we can take left from space

for i = 1 to range("A"&rows.count).end(xlup).row
if instr(range("A"&i)," ")>0 then

range("A"&i) = left(range("A"&i),instr(range("A"&i)," "))
endif
next i

 

[wittonlin]

It takes all possible considerations I could think of in account!

E.g.
Mr Joe Chambers Jr Chambers Jr
Goes to
Mr Joe Chambers Jr

<tbody>
</tbody>

 

[wittonlin]

I guess I lost Gallen.

Anyone know why I might be getting a runtime error 5, invalid procedure call or argument after it removes the name on line beginning with Range("A" & i)?

There's normally no more than a couple hundred rows, and I was testing on far fewer. I tried a few changes to how it counts row for the variable range, but no luck.

for i = 1 to range("A"&rows.count).end(xlup).row
if instr(range("A"&i),range("B"&i))>0 then

range("A"&i) = left(range("A"&i),instr(range("A"&i),range("B"&i))-2)
endif
next i

 

[wittonlin]

We got in a beautiful piece of code that's near perfect!

It handles the biggest initial struggle of dealing with name prefixes and suffixes!

The ONLY problem is on the occasion where the FULL name is in BOTH column A and B! It of course removes the entirety of the column A cell.

Dim rngCell 
For Each rngCell In Range("A1", Cells(Rows.Count, 1).End(xlUp)) 
    rngCell.Value = Replace(rngCell.Value, rngCell.Offset(, 1).Value, vbNullString) 
Next

The problem is that it's so simplified I'm not really sure what's going on. lol

This makes it impossible for me to reverse the code and run that new macro prior to the original above.

I was counting on handling this potential issue where the FULL name is in BOTH column A and B!

 

[wittonlin]

I finally see the problem.

The code above works because column B cell is a fixed value. And that fixed value is removed partially from column A cell when it matches.

When full name is in column A cell and column B cell the only way is go back to using delimiters and deleting before and after whatever delimiters, say the first space.

I'll try to re-write that code. I should just be able pull data after first space in column A and overwrite the column B cell. I'm just trying to figure out how to only execute the code when column A cell = column B cell or why bother?...just more resources and time.

By the way the only way I can even consider doing this is that I now have to remove ALL possible prefixes I can think of (Mr.,Mr,Mrs.,Mrs, etc.)

If ANYONE can help please do.

Thanks so much!

 

[gallen]

So your first issue would be to make sure you get the surname in column B first so you need to write a procedure to do just that. Would looking for all characters after the last space work?

This would do that and so need running before you then clean column A:

Sub GetSurnames()

    Dim l As Long
    Dim iSpace As Integer
    Dim sName As String
    
    'loop through all values in column B assuming first row of data is in row 2
    For l = 2 To Range("B" & Rows.Count).End(xlUp).Row
        'Remove any leading/trailing spaces
        sName = Trim(Range("B" & l))
        
        'look for the last space in the string
        iSpace = InStrRev(sName, " ")
        
        If iSpace > 0 Then
            'Parse out everything after the last space and write back to cell
            sName = Right(sName, Len(sName) - iSpace)
            Range("B" & l) = sName
        End If
    
    Next l
    
End Sub