Compare two columns and highlight the differences. Not using VBA code please

[John Louis]

Hello,
i have two columns in my workbook AC & AD data starting on row 3. what I would like to do is have excel look at column AC and compare it to column AD than have the alphanumeric string that isn't found in column AD and highlight it bold or color the string.below is copied from my tracker and I manually highlighted the missing string in column AC that was not found in column AD to give you a visual of what I'm trying to do. Thanks much.

[TABLE="width: 533"]
<tbody>[TR]
[/TR]
[TR]
[TD]Column AC
[/TD]
[TD]Column AD
[/TD]
[TD]NOTES
[/TD]
[/TR]
[TR]
[TD]PM
[/TD]
[TD]ED
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Note: My data is in columns
AC and AD and starts on Row 3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UNL24131R, UNL04131, NYJAU4131
[/TD]
[TD]NYJAU4131, UNL04131
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]


[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

I don't believe there's a way to apply formatting to a partial string in a cell, except through VBA. If you have Excel 365 with the TEXTJOIN function, you could try adding this formula in AE3:

=TEXTJOIN(",",TRUE,IF(ISERROR(FIND(TRIM(MID(SUBSTITUTE(AC3,",",REPT(" ",200)),{0,1,2,3,4}*200+1,200)),AD3)),TRIM(MID(SUBSTITUTE(AC3,",",REPT(" ",200)),{0,1,2,3,4}*200+1,200)),""))

This will create a list of strings from AC3 that do not appear in AD3. It will work for up to 5 strings in column AC, but can be easily raised by changing the array constants.

 

[sandy666]

I'd like to test it on more representative example

 

[John Louis]

Hi Eric,
This seems to work perfectly!!! could I ask you how to change the array constants up to 15? Or you could update the formula in your original post. again Thank you so very much!! John

 

[John Louis]

Thank you Sandy! Looks like Eric was able to help me out. I appreciate you looking at this for me...John

 

[sandy666]

Welcome

 

[Eric W]

This should work for up to 15 segments:

=TEXTJOIN(",",TRUE,IF(ISERROR(FIND(TRIM(MID(SUBSTITUTE(AC3,",",REPT(" ",300)),{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}*300+1,300)),AD3)),TRIM(MID(SUBSTITUTE(AC3,",",REPT(" ",300)),{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}*300+1,300)),""))

I also raised the space value from 200 to 300 just in case. If your values average more than 10 characters each, you may need to raise that some more. You can also use this version of the formula which is slightly shorter:

=TEXTJOIN(",",TRUE,IF(ISERROR(FIND(TRIM(MID(SUBSTITUTE(AC3,",",REPT(" ",300)),(ROW(INDIRECT("1:15"))-1)*300+1,300)),AD3)),TRIM(MID(SUBSTITUTE(AC3,",",REPT(" ",300)),(ROW(INDIRECT("1:15"))-1)*300+1,300)),""))

but you'll need to enter this version with Control+Shift+Enter.

 

[John Louis]

Thanks again Eric! I will run this tomorrow when I get to work. I really appreciate your help getting this working I was told it couldn't be done unless you used VBA code. Thanks John

 

[Livio]

Hello,
How it would be with VBA

I have 2 Worksheets ("Old" , "New" ).

I want to compare two columns of different worksheet

I want to highlight the cells on the column A of worksheet "New" the missing values from the column A of Worksheet "Old"

I am facing the following code:

Dim lastRow As Integer
Dim rng As Range
lastRow = Sheets("New").Range("A:A").End(xlUp).Row

For i = 1 To lastRow
Set rng = Sheets("New").Range("A:A").Find(Sheets("Old").Range("A2:A10000")
If rng Is Nothing Then
Sheets("New").Cells(i, 1). Interior.Color = RGB(250,0,0) 'I just want to highlight the cell on the column A ,is correct?
End If
Next