Identifying and highlighting non-matching text across 2 columns

[Ali_G999]

Hope you can help a first time poster (self taught at Excel).

I am working in Excel 2007 and have data in 2 columns (approx. 70000 rows) which contains details of 500 current & historical: Employee No., Role, Name & Office
I have cleansed, trimmed and matched so I know which rows do not match. However I need to be able to highlight the exact text which doesn't match ideally in bold and colour. For e.g. all text matches except Office which I can then record in a further column.

Is there a way to do this?

I cannot use text to columns as it would be extremely time consuming and the formatting of the names whilst consistent across the columns includes forenames, middle names, surnames including Mc, McRae, Mac Rae, Mc Rae would invovle a lot of reformatting/concatenation etc of the new columns.

I have a feeling that I need a formula to use in conjunction with conditional formatting but I have looked everywhere and can't see a formula I could use.

This is my last hope of trying to get it sorted

 

[Momentman]

Welcome to the forum

Can we have SAMPLE data showing how the data is now and what your expected RESULT should look like?

 

[Ali_G999]

Apologies but I cant see how to attach a file? I've tried to screenshot an example data set it but it won't let me attach as its not an image

 

[Momentman]

Apologies but I cant see how to attach a file? I've tried to screenshot an example data set it but it won't let me attach as its not an image

unfortunately, you cant attach files here. What you can do though is to upload it to dropbox or google docs and paste a link here for whoever needs to access, making sure you modify the access rights appropriately

 

[Ali_G999]

Unfortunately I won't be allowed to do that but a sample of what the data looks like and what I'm hoping to achieve would look like this but with the bold text also highlighted in colour

[TABLE="width: 581"]
<tbody>[TR]
[TD]Previous Data
[/TD]
[TD]Current Data
[/TD]
[TD]Matched
[/TD]
[/TR]
[TR]
[TD]0145325 Clerk John Murphy Wingford
[/TD]
[TD]0145325 Clerk John Murphy Wingford
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]156346 Analyst Joseph McDonald HQ
[/TD]
[TD]156346 Manager Joseph McDonald HQ
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]2534468 Officer Alison Keyes Springfield
[/TD]
[TD]2534468 Officer Alison Keyes Stoke
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]178596 Admin Jane Doe HQ
[/TD]
[TD]178596 Clerk Jane Doe Metro
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]0145325 Clerk John Murphy Wingford
[/TD]
[TD]0145325 Clerk John Murphy Wingford
[/TD]
[TD]Yes
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Momentman]

Please try this code on a COPY of the data and maybe not the 70,000 rows. you could copy a section and run the code on it

Sub HighlightMissingWord()    Dim Ask, I As Integer, Pos As Integer, J As Integer    For I = 2 To Range("A" & Rows.Count).End(xlUp).Row        If Range("A" & I).Value <> Range("B" & I).Value Then            Ask = Split(Range("B" & I).Value, " ")            For J = 0 To UBound(Ask)                On Error Resume Next                If InStr(1, Range("A" & I), Ask(J)) = 0 Then                    Pos = InStr(Range("B" & I), Ask(J))                    With Range("B" & I).Characters(Pos, Len(Ask(J)))                        .Font.Bold = True                        .Font.Color = vbRed                    End With                End If            Next J        End If    Next IEnd Sub

 

[Momentman]

Unfortunately I won't be allowed to do that but a sample of what the data looks like and what I'm hoping to achieve would look like this but with the bold text also highlighted in colour

I hope the code pastes better this timeN.B: Can't get the code to paste as desired

 

[Momentman]

The code should look like this

Sub HighlightMissingWord()
    Dim Ask, I As Long, Pos As Integer, J As Integer
        
    For I = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Range("A" & I).Value <> Range("B" & I).Value Then
            Ask = Split(Range("B" & I).Value, " ")
            For J = 0 To UBound(Ask)
                On Error Resume Next
                If InStr(1, Range("A" & I), Ask(J)) = 0 Then
                    Pos = InStr(Range("B" & I), Ask(J))
                    With Range("B" & I).Characters(Pos, Len(Ask(J)))
                        .Font.Bold = True
                        .Font.Color = vbRed
                    End With
                End If
            Next J
        End If
    Next I
End Sub