ID Comparison

[KamenRider]

Hi Guys,

I would like to ask for your ideas on how I am going to compare the excel files provided in a list. A list is given to us (10 IDs or more) that is being manually check in the shared drive. I would like to have it automated by reading all IDs until nothing to compare. The output should look like this: 

 

This is the sample list from our partner (attaching the ID Compare). This means Column A vs Column B :

Any additional space is considered mismatch. 

Please let me know if you have any question. Hoping you could help and give me ideas on this.

Thanks,

Kamen

[caltang]

I’m on my phone right now so I can’t see every data set. But from the image and files, it looks like your data exists in both multiple files and multiple tabs. The first order of business is to load all the files and tabs into one tabular format with the file name and sheet name visible. 

Then it’s just a matter of splitting ID 1 and 2 with a select tool and use a Join - if there’s multiple similar codes you’ll need to make a key that is unique to prevent many-to-many or 1-to-many/many-to-1 joins. 

[KamenRider]

Hi @caltang 

Thanks for the response. Appreciate if you could send me a workflow to see. Can you have it something that will run back and forth until it completed all IDs to compare? Each IDs will reflect in the tabs. Please see attached sample output.

Thanks and hoping to hear from you and to everyone.

Kamen

[Qiu]

@KamenRider 
First thing we need to do is to collect all the Excel files and read in the contents in the share drive, which can be done by a macro.

And then some Joins and Compare. 😁
I have left some details such as unmatch Description, which will be from the "L" and "R" anchors of the last Join tool.

[KamenRider]

HI @Qiu 

Thanks for your response. I tried the workflow in my live account and here's my suggestion.

1. It is okay for the description (left) to be NULL since we also need to compare it with description (right) if it is also NULL or not. They can also be mismatch. So I think the filter tool is not necessary if that is what I understand in the workflow.

2. In my live results, I encountered duplicates. Can you help me remove it? I can't figure out to remove it and makes the results worse when I adjust or configure it. You may find the correct field at the bottom with "True" value as per snapshot below.

 

3. Can you also help produce the output file like the attached excel file I attached previously? All "IDs vs"  should be saved per tab in the spreadsheet.

Looking forward for your assistance.

Thanks,

Kamen