Reconciliation- data in excel tabs

[vishtrack]

Hi All,

I have a problem which I'm trying to solve and need help. Attached excel for reference.

- I receive a reconciliation file on excel daily, with data in multiple tabs

- 'Main' tab contains data which acts as a reference for other 'Mirror' tabs

- I need to do a reconciliation of data set in each tab keeping Main as base

- I need to know which all additional line items are present in any of the mirror tabs that are Not on Main. I also need to know which Mirror tab does it pertain to

- I also need to know if there are any additional line items in any of the Mirror tabs that are not available in Main

Thank you in advance!

[WirkKarl]

Hey! I’ve tackled similar reconciliation tasks before. One approach is to use the Join tool for each Mirror tab against the Main tab—this will let you see unmatched rows. You can also add a field with the tab name before appending all results, so you know which Mirror tab each extra line comes from. From there, a Union tool and a little filtering should give you exactly the items not present in Main. Hope that helps!

[dreldrel]

If the sheet names are always the same, you can use multiple input tools to bring your data, and use the join tool with your key joining columns. The right & left output anchors are the data that is not reconciled

[vishtrack]

I want to have it as a single input source and the tabs names are dynamic

[Raj]

@vishtrack 
As you have mentioned you have a static main sheet and dynamic extra sheets 
you need to create a batch macro to match the data from all sheets

first you will pull the main sheet
Next you will pull data from all the sheets and into batch macro you will go sheet by sheet and you will have the output desired

Please feel free in case of help with building workflow required.

[vishtrack]

Hi @Raj , I need help.
Say I am able to bring data from multiple tabs in one place using dynamic input (and the data looks like the attached file), how do I get to do a reconciliation to look like the output file attached which shows the missing rows in 'mirror1' and 'mirror2' tabs? Thanks in advance