Combining 2 Data Sets and Calling out Changes

[adrian1713]

Hello,

I am trying to compare an old set of data with a new set of data and want the output to call out any fields that have changed, whether that’s a change in value in a prior existing cell or new rows that have been added.

The Column headers will always be in the same order. (eg. First name --> Last Name --> Address --> Zip --> City)

The real data set contains 50 Columns but I am only looking to compare 8 of them.

There is not a primary key that can be used, so I was wondering if there was a way to combine the first name column and last name column and make the combination of the values (eg . Alex + Jones and Alex + Smith) the primary key.

For Example: Having the output call out changes made in each row in regards to Last Name, Zip, and City. (It would not call out changes made to First name and Address).

Old data:

First Name	Last Name	Address	Zip	City
Alex	Jones	123 Candy Ln	11111	Chicago
John	Smith	245 Lake St	12345	New York
James	Doe	1 Michigan Ave	22222	Boston
Alex	Smith	111 Park Ave	11112	New York

New data:

First Name	Last Name	Address	Zip	City
Alex	Jonesx	123 Candy Ln	111119	Chicago
John	Smith	245 Lake St	12345	New York
James	Doex	123 Michigan Ave	22222	Boston
Alex	Smith	111 Park Ave	11112	New York
Johnny	James	111 Lake St	60600	New York

Output:

Row	Column	Original Value	Changed To
1	Last Name	Jones	Jonesx
1	Zip	11111	111119
3	Last Name	Doe	Doex
5	Last Name		James
5	Zip		60600
5	City		New York

Thank you very much, I appreciate all of the help in advance.

[Luke_C]

Hi @adrian1713 

Here's an example of how I usually approach this:

1. Transpose both datasets
2. Join on ID and Field Name
3. Review the J output for changes
4. Review the L and R output for additions and deletions

[IraWatt]

Hey @adrian1713,

I couldn't quite manage to copy all your values in so I just wrote a few, but the logic seems to work:

If you have any questions or issues with the workflow make sure to ask :)

HTH,

Ira 

[DataNath]

The following works with the 2 tables you've provided, including the checks only on the 3 fields of interest:

[adrian1713]

Hi everyone, thank you for the swift responses. I actually just edited the tables in my post. There isn't actually a column named Row, I just want the information to be called out with some unique identifiers.

[Luke_C · Accepted solution]

Hi @adrian1713 

Here's a solution that keys off the 2 fields you are not comparing, first name and address. Without a primary key this is far less reliable in my opinion, but under the assumptions that first name and address do not change between datasets, and that people with the same first name don't share an address, it should work fine.

[IraWatt]

Hey @adrian1713,

To get this to work I think you'll need a unique identifier? How do you identify which elements belong to each person?

Why not just add in a row ID to @Luke_C's solution using the record ID tool?

[adrian1713]

Is there a way to create an unique identifier by combining 2 columns, for example Last Name and First Name.

I originally wanted to use record ID but realized that there would be a problem if a new row is added in the middle of the data set or if the rows are reordered causing the rest of the data to be thrown off. 

[Luke_C]

Hi @adrian1713 

My solution creates a unique identifier based on first name and address (the two fields you're not comparing). I don't think last name would work since you're evaluating changes there.

Let me know if the solution above works for you.

[DataNath]

Without spending a lot more time on it, I've managed to call out the changes. However, this is super bruteforced and wouldn't really be dynamic if you ever have the same first + last name and then the same changes occurring to them. You really need a Unique ID as stated previously. Have attached the updated workbook in case it's useful.