VBA / Compare two columns and show differences in a third column

[ariel_733]

Hello ?

I would like to compare 2 columns populated with a lot of text in each cell, and then have inserted on the right a third column that would mark the differences between the two selected columns.
For example, I have some outdated text in column A, and some newer, amended text in column B. What I want is a new column to be inserted on the right, populated with the same text in B, but with all the differences from column A marked as follows:
1. deleted text in red and strikethrough
2. new words added, not present in A, in blue.

Thank you in advance!

 

[Dermot]

You can do it without code, like this

I tested with data in B3:B14 and C3:C11 - change these to your ranges

This formula will append the two ranges one above the other and then remove duplicates. In other words, it will give you a unique list of the items in both columns.

=UNIQUE(LET(s,SEQUENCE(COUNTA(B3:C14)),IF(s<=COUNTA(B3:B14),B3:B14,C3:C11)))

I put this formula in F3

You can use conditional formatting to colour this combined list. It needs two formulae, one for each colour
For the items which have been removed (were in list 1, are missing in list 2), I used this formula with conditional formatting for F3, the first cell of the combined list

=(IFERROR(MATCH($F3,$C$3:$C$11,0),0)=0)

and set formatting to red, strikethrough, and I copied this formatting down the column
For the items which have been added (are in list 2 but not in list 1), I used this conditional formatting formula for F3 (and set the colour blue and copied formatting down)

=(IFERROR(MATCH($F3,$B$3:$B$14,0),0)=0)