Forum Discussion

jonathanrevops's avatar
jonathanrevops
Copper Contributor
Nov 01, 2023

Comparing worksheets based on multiple columns to determine differences

I am looking to determine if there is built in functionality to quickly see the differences between two different spreadsheets.

 

Use Case:

I have two separate systems that are supposed to have the same data, but it looks and each captures slightly different data.

System 1: Customer Orders with payments and open balances

System 2: Customer Orders with items ordered 

 

I want to reconcile between them to see if there are customer orders that live in only one of the systems as I don't entirely trust that either has everything.

 

I don't have a unique ID in this case, but I can match them up manually based on the patient email, date of the order, and billing zip code, which are stored in separate columns.  The date format may be inconsistently stored.

 

Is there a native tool in excel that will highlight orders that aren't in the other data set or create an output spreadsheet showing those differences?

 

Present solution is to concatenate the columns that I am using to create a unique identifier column, copy each of them below the other spreadsheet,  and then use highlight duplicates, and filter, but it is inefficient and error prone and can create false positives.

 

I am on Excel for Mac version 16.78 presently.

Resources