Comparing worksheets based on multiple columns to determine differences

[jonathanrevops]

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.

[#2]

built in functionality to quickly see the differences between two different spreadsheets.

What kind of buildin do you mean?
Maybe Power Query or VBA or some sql?

[#3]

https://techcommunity.microsoft.com/t5/excel/align-the-column-values-with-each-other-so-you-can-easi...

[#4]

I was hoping that excel would have native tools where you could say in worksheet 1, combination of columns 1 and 2 (and 3 and 4 if needed) provide a unique identifier - perhaps it would tell you how many you needed to pick for unique values. You would select the equivalent columns of data in the second worksheet, and the result would be to identify all of the rows that are in both sheets, and also output the unique rows in each sheet as well as any that were unable to determined due to duplicate "unique values"

[#5]

I am afraid if you upload your workbooks more talent will join the discussion as below.

https://techcommunity.microsoft.com/t5/excel/highlight-duplicates-in-sheets-with-2-criteria/m-p/3983...