3 Way Data Reconciliation

Hello,

I am trying to reconcile data between 3 different files at once and I was able to get all 3 column values transposed together and assign Group column numbers but I am not able to reconcile them together. Is there an alternate way or is it that m doing it wrong, please help ? Please see below sample for details.

I am using Multi row formula and formula is as per below which I feel is not correct, do I need to creat multiple formulas for 3 way recon.

if [Group Column Number] = 3 then if [Value] = [Row-1:Value] then "True" else "False" endif else "-" endif

Expected results as below:

File Name Value Group Column Number Results

A Lion 1 Value Matches with File C but not with File B

B Whale 2 Value does not match with File A and File C

C Lion 3 Value Matches with File A but not with File B

A Tiger 1 Value Matches with File B and C

B Tiger 2 Value Matches with File A and C

C Tiger 3 Value Matches with File A and B

Thanks

Regex

Python

Data Investigation

Accepted answers

atcodedog05

Hi @skotian1289 ,

Your scenario is bit complex then i had expected and here is the workflow.

Input: Files given by you

Output:

Hoping record ID is not important. And you can change the result text in formula tool.

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind😀👍

3 Way sample.zip

All comments

paulfound

Hi @skotian1289

Think this is what your after, I used the multijoin tool by position just add the files together then did a formula across them.

skotian1289.yxmd

skotian1289

Hi Paul,

Thanks for the response the problem with below solution is I have around 1000s of columns in 3 files with different header names hence I will have to create formula for each off them hence I transposed the data to segregate them as Name and values. Your solution will work only if my column header is constant and I can apply formula using those headers. Correct me if m wrong.

Thanks

skotian1289

Paul - It was my bad I did not give you proper sample. See below

File Name Value Group Column Number Results

A Lion 1 Value Matches with File C but not with File B

B Whale 2 Value does not match with File A and File C

C Lion 3 Value Matches with File A but not with File B

A2 Tiger 1 Value Matches with File B and C

B2 Tiger 2 Value Matches with File A and C

C2 Tiger 3 Value Matches with File A and B

A3 Tiger 1 Value Matches with File B and C

B3 Tiger 2 Value Matches with File A and C

C3 Tiger 3 Value Matches with File A and B

ChrisTX

Can you provide an Excel example of your actual input data? Starting from the processed data you provided may not be the best option.

You don't have to provide 1000 columns, but providing a representative sample of the initial data, complete with the different column names, may help.

Chris

skotian1289

Hey Chris,

Sure here is the attached, I have provided sample of 3 input files and also a sample of a processed file i.e till what point my current tool is able to process the data. Hope this helps.