Data Comparison take three, equals sign and HYPERLINK
This is a series of articles with solutions that evolve as Excel evolves.
- Data Comparison take one, AND or EXACT
- Data Comparison take two, EQUALS
- Data Comparison take three, EQUALS and HYPERLINK
- Data Comparison take four, PivotTable
- Data Comparison take five, Power Query
Excel 365 (Jan-2020+)
Do you need to compare two lists of data in Excel and find differences?
How can you do it quickly? There are two previous takes on it, this third one was developed by Wyn Hopkins of Access Analytic. He has a video about it on YouTube
The final formula used looks complicated, as it uses the ADDRESS, CLEAN, COLUMN, HYPERLINK, IF, ROW, TEXTJOIN and TRIM functions. Building it up in parts makes it easier to understand.
Starting out
First copy your original data into a worksheet.
Copy the new data into the same worksheet, next to the original data.
Sort both lists into the same order.
We are going to use the equals sign (=) to do the comparison and then build up the formula
=A3:B6=D3:E6
When you press enter, the formula will ‘spill’ across the comparison area. It’s dimensions matching the original data. The formulas in the spill cells will be greyed out in the formula bar. If the formula needs to be changed, the change needs to be made in the top left cell of the range.
If the cells match then the formula returns TRUE otherwise it returns FALSE. The comparison ignores the case of the data.
The comparison of cell B3 (“Northside”) and cell E3 (“Northside “) shows as FALSE because of a trailing space in cell E3.
Add an IF function
Surround the formula in an IF statement, that returns nothing if the data matches and the word ‘Difference’ if it does not.
=IF(A3:B6=D3:E6=TRUE,"","Difference")
Add an ADDRESS function
Instead of returning the word ‘Difference’, use the ADDRESS function combined with ROW and COLUMN to return the cell address for data that does not match.
=IF(A3:B6=D3:E6=TRUE,"",ADDRESS(ROW(D3:E6),COLUMN(D3:E6)))
Add a TEXTJOIN function
Instead of having the cell address for data that does not match spill across the data range, join them together and return them in a single cell.
=TEXTJOIN(", ",TRUE,IF(A3:B6=D3:E6=TRUE,"",ADDRESS(ROW(D3:E6),COLUMN(D3:E6))))
Add a HYPERLINK function
Now add a Hyperlink, so that clicking on the results cell highlights the data that does not match.
=HYPERLINK("#"&TEXTJOIN(", ",TRUE,IF(A3:B6=D3:E6=TRUE,"",ADDRESS(ROW(D3:E6),COLUMN(D3:E6)))))
Add a CLEAN and TRIM function
Finally clean and trim the data before comparing it.
Some data will have extra spaces or use spaces for padding. This will cause the formula to return the cell address.
In the screenshot, an extra space has been added to the end of the address in the first row. This isn’t visible unless you edit the cell.
If you consider the data as matching regardless of the spacing, the data can be cleaned before comparison with CLEAN and TRIM functions.
CLEAN removes non-printable characters. TRIM removes any leading or trailing spaces as well as reducing all internal spacing to single spaces.
=HYPERLINK("#"&TEXTJOIN(", ",TRUE,IF(TRIM(CLEAN(A3:B6))=TRIM(CLEAN(D3:E6))=TRUE,"",ADDRESS(ROW(D3:E6),COLUMN(D3:E6)))))
Highlighting the differences
Click on the hyperlinked cell that you have created.
The cells with differences will be selected. On the Home menu tab, click on the Fill Colour icon and select a colour.
The cells with the differences will be highlighted.
This method works best if the data set is not large and does not have a high number of differences. Otherwise it would be best to use the method in Data Comparison take four or five.