Compare two columns from two different data sets

danniboy3000

Hi all,

I am brand new, taking the courses, and learning fast. Great tool!

I have two data sets with millions of rows that should contain the same set of data. I am trying to find the differences.

For both sets I concatenated three columns to create an unique ID column.

I want to compare the two unique ID columns to see the % match / mismatch.

How do I do that? Is there a way to see the % match between the two? The below data table is an example of what I am talking about.

Customer Name	Customer ID	Number of purchases	Concat 1
John	1	10	John110
Bob	2	11	Bob211
Sally	3	12	Sally312

Customer Name	Customer ID	Number of purchases	Concat 2
John	1	10	John110
Bob	2	11	Bob211
Jenny	4	13	Jenny413

Gawa

hi @danniboy3000 

You want to know how many columns are match for each row? For example, 2 out of 3 columns match=> 66.7%, 1 out of 3 columns match=>33.3%, correct?

If so, you can transpose table and compare them by JOIN and identify difference by formula tool...so on. Please see attached workflow.

By the way, I suppose you create unique column by concat first three column, however, I recommend to put a separator like semi-colon ;  vertical bar| etc..

For example, if there is another record like:

Customer Name: John, Customer ID: 11, Number of purchases: 0

Then, concat value would be John110, which is identical to first record, and not a unique one! By using separator, they look like;

John;1;10

John;11;0

These values become unique.

1189884_gawa.yxmd