Comparing between excel columns

Question

Hi I am trying to compare two excel files between all the columns like 37 columns. I need to check the differences between both files. I tried adding record ID and then doing a transpose and join. That works but what it does it also flags those records that are common to both files but are in different rows.

Data set 1

ID Date Owner SNO

1 mm/dd/y SK 100

2 mm/dd/y SY 101

3 mm/dd/y SZ 102

Data Set 2

ID Date Owner SNO

1 mm/dd/y SY 101

2 mm/dd/y SZ 102

3 mm/dd/y SK 100

Record ID( ID is what i have added) , in my solution i am also getting Owner SK as flagged as ID has changed but the data is the same. I only want the data that has changed from first to second file in any of the columns

Lunamoona0 · Answer

This specific equation thinks about the qualities in cells B2, C2, and D2. Assuming that the qualities are all equivalent, the equation brings Equivalent back for Comparing 3 excel columns. In any case, it returns Not Equivalent.

clmc9601 · Answer

Hi @SouravKayal,

Rather than doing a plain RecordID, I would create an identification column from the data.  Perhaps use an expression like the following:

ToString([ID])+[Owner]+ToString([Date])

Then it won't matter what order the rows are in. Just be sure to add enough columns to the expression that it's a unique identifier (otherwise you'll cross multiply your data when you join it back).

If this helps, please consider marking it as a solution so others may find it.