Highlight differences between two columns, a1 b1

John Louis

New Member
Joined
Aug 10, 2018
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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!!
 
Last edited:
Upvote 0
Try this:-
Code:
[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
 
Upvote 0
Thanks Mick! I assume this code can be copied & pasted, but honestly I don't know where in excel to enter it��
 
Upvote 0
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
 
Upvote 0
Thanks again Mick. I had no luck with code. But I really appreciated your input.. Thanks John
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top