Combine two tables under certain conditions

[mgspk01]

Hello.

I'm new to this forum, but hopefully someone will be able to help me with this task, which is very important for my work.

There are 2 tables for different months with the same set of columns and a lot of rows. Number of SYSNAMES for each SYSCODE may vary (SYSNAME may be new or deleted).

I need those tables combined under the following conditions:

a) rows from TAB2 that are not in TAB1 (identifiers would be SYSCODE and SYSNAME) must be added into resulting table and highlighted
b) resulting table must have a %-column from TAB2
c) resulting table must have a new column for a simple math (%TAB1 minus %TAB2)

Basically, it supposed to look like on the picture. I tried to apply and customize various solutions I was able to find on the Internet, but with no avail. My level of scripting is not so good.

Table.JPG

Can anyone look at this case and help me to automate the process as much as possible? It's just too many rows in those tables to process them manually.

Any help will be much appreciated.

[Ron Coderre]

If you are only interested in Tab2 records that are not in Tab1
then maybe you could just put formulas down the right side of Tab2

• One formula enters the Tab1 value (if it exists), otherwise 0
J3: =SUMIFS($D$3:$D$9,$A$3:$A$9,F3,$B$3:$B$9,G3,$C$3:$C$9,H3)
Copy that formula down through J11
• The next formula performs the subtraction
K3: =J3-I3
Copy that formula down through K11

Is that an approach you can work with?

[mgspk01]

Hi, Ron. And thank you for your reply.

I looked into similar solution, it's working in its core, but hardly applicable for my task, I guess. TAB2 may contain less rows than TAB1 and vice versa, and I need a single resulting table to contain all the entries.

Furthermore, TAB1 and TAB2 will probably be located on a separate sheets.

[mgspk01]

Hi. Can someone help me with my task as per description above? Anyone? This will become quite urgent soon, and I'm surely won't be able to do this task manually.

[mgspk01]

I though I attached my workbook in the first place, but just now realized there was a screenshot only. Attached my workbook. Please help to solve my problem.

[Ron Coderre]

From my perspective, the best way to combine the two lists is with MS Query and a summarized Union Query.
(See the attached file)
• Create this folder on your hard drive: C:\Downloads
• Set the Tab1 range name to rngTab1
• Set the Tab2 range name to rngTab2
• Save the file in that location
• Select rngTab1
• Data.From_other_sources
...From MS Query
...Data_source: Excel Files...Click: OK
...Database_Name: select the file
...Add rngTab1 to the right-side panel...Click: Next
...Click: Next...Click: Next...Click: Next
...Check: View Data or Edit query in Microsoft Query...Click: Finish
...Click: SQL button
...Enter this code in the SQL window (be sure to edit the file name and/or location to match what you used):

Please Login or Register  to view this content.

...Click: OK
...Return data to Excel

The end result will be this:

Please Login or Register  to view this content.

If the source data changes, just save the file and refresh the query data.
If you need to edit the query...
• Select any cell in the query data range
• Data.Connections....Click: Properties...Click: Definition tab
...Edit the Command Text


Is that something you can work with?

[mgspk01]

Hi, Ron.

This solution works perfectly for me. Many thanks! That is very efficient way to go with this task, and very adaptable too. Appreciate your help.

There are minor details left that I'd like to complete, though.

a) my task requires an additional column resulting %TAB1 minus %TAB2 operation; I know I can do this via formula in one cell and then drag it down, but is it possible to perform this more efficiently, without dragging down the formula?

b) if there will be columns like "System Name" (with spaces) do I need to change them to something without spaces in your MS Query? I tried that earlier by replacing SYSNAME with 'System Name', but query doesn't seem to work.

c) is it possible to create a script or macro to perform the following actions automatically or with minimal interaction: execute saved query -> export to spreadsheet -> calculating new column with a simple math (as in point a)? If yes, how can this be done?

[Ron Coderre]

a) my task requires an additional column resulting %TAB1 minus %TAB2 operation; I know I can do this via formula in one cell and then drag it down, but is it possible to perform this more efficiently, without dragging down the formula?

(See below comment)

b) if there will be columns like "System Name" (with spaces) do I need to change them to something without spaces in your MS Query? I tried that earlier by replacing SYSNAME with 'System Name', but query doesn't seem to work.

If column headers have spaces, they must be enclosed in single-quotes (')

c) is it possible to create a script or macro to perform the following actions automatically or with minimal interaction: execute saved query -> export to spreadsheet -> calculating new column with a simple math (as in point a)? If yes, how can this be done?

(See below comment)

Comment:
I've been mulling over publishing an Excel app I wrote that consolidates data ranges. Your question gave me the excuse I needed.
The attached file allows you to define a SQL query that extracts Named Ranges from closed Excel Workbooks. In your case, it melds the two tables together and calculates the %Tab1 - %Tab2 difference.

Is that something you can work with?

[mgspk01]

Hi, Ron.

Works like a charm! Many thanks for your solution - I was able to adopt it for my needs perfectly. So now I can say that my problem is finally solved.