How to reconsile all fields within two spreadsheets

[Hoss2434]

Hello all,

I was wondering if anyone could provide some insight as to their best method of reconciling every field between two different spreadsheets.

For example:  The two tables below show that the ID field has a Color and Month field associated, in this case, the ID field is a unique identifier. 

When comparing the two tables we can see that the second spreadsheet shows the following discrepancies:

1. Missing row for ID 2
2. Blank Month for ID 3
3. Different Color field for ID 1

ID	Color	Month
1	Red	May
2	Blue	Feb
3	Green	Jan
4	Orange	June

ID	Color	Month
3	Green
1	Green	May
4	Orange	June

In a real-life example, I have hundreds of rows and about 20 columns to reconcile between spreadsheets on a daily basis. What would be the most practical way to reconsile them in a workflow?

[Deano478]

@Hoss2434 For this kinda work your best best is the transpose tool group in the key column which would be id and leave the rest of values as column values and join them to find discrepancies. Have a look at the attached as an example

[PhilipMannering]

Transposing is the way to go, as @Deano478 says.

You can also test my "Only Match" macro...