Merging 2 tables with multiple conditions

[NeilSwithin]

Hi,

I am kind of stuck with a scenario for which I'm not able to resolve. I have 2 input tables with 2 common columns. The output must be 1 table with a merge of results from both tables. Each Sub-LOB should show individual totals and the compliance % per Sub-LOB must be displayed along with the other columns causing the non-compliance numbers and relevant control managers. Its pretty complicated, any help with this would be much appreciated. Thanks !

[usmanbashir]

@NeilSwithin - I see some oddities in your Input 2 within the [In Business CDO] field. Specificlly #REF!, TC, and 'align to...'. Those don't appear in your result table. So I'm a little confused on your data set.

[Qiu · Accepted solution]

@NeilSwithin 
I think I understand the situation here.
If we do the joint right away, it will result in a 1 Vs many situation, which is not we need.

I am using sample tool as below and hope this is what you need.

[NeilSwithin]

@usmanbashir- Apologies, I have corrected the inputs. Please refer the attachments.

[NeilSwithin]

Thank you for your response. Solution was almost good, but was not matching the result table. I tried to join the left and right output of the join tool into the union tool which got me closer to the result. But needs further sorting and totaling of columns. Appreciate if you could help. 

[usmanbashir · Accepted solution]

@NeilSwithin - I think I was able to get it. I did see a couple #'s not matching with your result table, but makes sense when referring to Inputs 1/2. It's a little messy, but hope this is what you were looking for!

[NeilSwithin]

Thank you so much, this works !