Highlight differences between two columns, a1 b1

[John Louis]

Hello, I have two columns each containing hundreds of rows, each cell in the rows contain several alphanumeric strings separated by a comma. What I would like to do is in the first column, column A cell 1 is have excel look at all the data in cell a1 and compare it againest the second column, column B cell 1 and have it highlight the unique (differences) in each cell. I also want to be able to apply this to all rows in each column. I tried conditional formatting but it doesn't like the alphanumeric strings I think. Thanks for any help you can provide I appreciate it. Thanks John

 

[DSCfromCFA]

G'day John,

I suspect that what you want to achieve is going to be difficult, but it may help if you can give some indication of:

1. the nature of the cell contents including the consistency or otherwise of each cell

2. what level of variance you are expecting to find between the columns, and if that variation is limited in location within the strings

3. the number of variations you are expecting to find.

4. whether this is a once only function, or a repeated/regular function

I ask the last two questions as the EXACT function can identify a variation between cells, and if you are only expecting a low number of variations and its a once only type job, then all you really need is to identify the cells that don't match and fix them manually, which is not an option if you regularly have to compare lots of entries.

Cheers

shane

 

[John Louis]

Hi Shane,
thanks for your reply! Here is an an answer for your questions.
1 here are a couple examples of cell contents, each cell can contain from 2 up to 10 different strings. Ex: unl23484r ,unl03484 I need excel to look at these strings in a1 and compare them to the strings in b1 and show the differences between the two.

2 the variations vary greatly, in the example above both begin with unl than one has a zero the other has a 2 and ends with letter R.

3 fron 1 to 10 or more strings per cell

4 this is a function I will need to update once a week as the data constantly changes.
Thanks again!!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG20Jun28
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] t
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Range("A:B").NumberFormat = "@"
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dn.Value = CStr(Dn.Value)
    [COLOR="Navy"]For[/COLOR] n = 1 To Len(Dn.Value)
        
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Characters(n, 1).Text) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Characters(n, 1).Text, ""
        [COLOR="Navy"]Else[/COLOR]
            .Remove Dn.Characters(n, 1).Text
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n

    [COLOR="Navy"]For[/COLOR] n = 1 To Len(Dn.Offset(, 1).Value)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Offset(, 1).Characters(n, 1).Text) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.Offset(, 1).Characters(n, 1).Text, ""
        [COLOR="Navy"]Else[/COLOR]
            .Remove Dn.Offset(, 1).Characters(n, 1).Text
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n

    [COLOR="Navy"]For[/COLOR] n = 1 To Len(Dn.Value)
        [COLOR="Navy"]If[/COLOR] .Exists(Dn.Characters(n, 1).Text) [COLOR="Navy"]Then[/COLOR]
            Dn.Characters(n, 1).Font.ColorIndex = 3
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n

    [COLOR="Navy"]For[/COLOR] n = 1 To Len(Dn.Offset(, 1).Value)
        [COLOR="Navy"]If[/COLOR] .Exists(Dn.Offset(, 1).Characters(n, 1).Text) [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, 1).Characters(n, 1).Font.ColorIndex = 3
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
.RemoveAll
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[John Louis]

Thanks Mick! I assume this code can be copied & pasted, but honestly I don't know where in excel to enter it��

 

[MickG]

Try this:-
To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.
Regrds Mick

 

[John Louis]

Thanks again Mick. I had no luck with code. But I really appreciated your input.. Thanks John

 

[MickG]

Try this example file !!!
https://app.box.com/s/rkqd8b8cf6nbt7ztdnvmazuhmcbs9lcc

 

[DSCfromCFA]

G'day John,

As I suspected this is more complex than I have the capability to assist with as it requires VBA, which MickG has already offered a potential solution for, and on that matter it may be worthwhile getting back to MickG as to what didn't work with his code - I have found that the experienced users on this forum have amazing skills in resolving these sorts of matters

Another possible option is to install Power Query from Microsoft (a free add-on (now called Get and Transform for Excel 2016 and later)) that may offer some ways to manipulate your data to make the differences you are seeking more obvious. - although I am as unfamiliar with that as I am with VBA.

Good luck,

shane

 

[John Louis]

HI Mick, sorry for my delayed response. Apparently at my job I'm not able/allowed to use VBA. You were super helpful & I appreciate it. I appoligize for any Inconvenience I caused. Thanks John