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
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.
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.