Merge tables with x & y exceptions

[Eranova]

Hey guys,

Hope you are well. Quick question: basically I have 2 tables on two defferent sheets

- Both have the same column headers (example: Customer number,Revenue, Costs, Gross profit, Signings).
- First table is for let's say Q3 and second is for Q4.
- In column A I have the customer number that often matches between the two tables, nevertheless I have customers that are not present in both Q3 and Q4 statements. Let' say in Q3 I had revenue from Google but not in Q4 anymore. While now in Q4 I have two new clients and revenue stream from Amazon and Facebook.

What I want to do is two merge both these tables taking into account the exceptions and adding an "under-header" Q3 and Q4. At first I wanted to Run query and stuff but I imagine it wont do this exception.

More precisely I want my new merged table to incorporate an "under-header" with a column for numbers coming from Q3 and numbers coming from Q4 while in parrallel adding horizontally my new lines of customers.

I have tried with VBA but I am a real beginner and don't have enough expertise I am going nowhere since 3hours.

Do you guys have any hints on how I could solve that?

Thanks in advance for your help&time :)

[Olly]

This sounds pretty straightforward with Power Query (Get & Transform Data), but it will be much easier to demonstrate if we see the structure of your workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Eranova]

Hey,

Thanks for your detailed answer. Here is a mock copy of what I would like to do. 3 sheets, 2 different tables and 1 sheet for merged.

Cheers!

[Olly]

Okay - this is made a bit more complicated as your table headers span two rows, and your data is denormalised (looks like a report output - would be nicer to hit the data behind that report directly!)

First step: Format your two datasets as tables - with no headers. Doesn't matter what the table names are.

Now, in Power Query, add the following queries:

fnNormalise:

Please Login or Register  to view this content.

Combined Tables:

Please Login or Register  to view this content.

Close and Load To 'Only Create Connection' and 'Add this data to the Data Model'

Now you can use PowerPivot to create a flattened pivot table to suit your required output.

See attached file for worked example.

[Eranova]

I am always surprised at how people are nice & dedicated on this forum. It's incredible. Thanks a lot for your time & help this is perfect.

Cheers!

[Olly]

Happy to help! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also 'add reputation' to those who helped you. Thanks.