Pulling and modifying data points from multiple external workbooks

[MontgoJG]

Greetings,

I am creating a workbook to consolidate data from two other workbooks created by exporting reports from an accounting system. Here are examples from the two exported workbooks:

WORKBOOK A
[TABLE="class: outer_border, width: 150"]
<tbody>[TR]
[TD]ItemCode[/TD]
[TD]InvValue[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0.00
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]619.42[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]289.56[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]4,459.33[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]44.59[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]153.40[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]139.18[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]56.88[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]794.82[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]650.37[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]184.38[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]368.76[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1027.71[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]355.45[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]543.19[/TD]
[/TR]
</tbody>[/TABLE]


WORKBOOK B
[TABLE="class: outer_border, width: 150"]
<tbody>[TR]
[TD]ItemCode[/TD]
[TD]InvTrans[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]-130.81[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]-420.15[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]-3,354.76[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]-1087.42[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]-12.50[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]-627.66[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]-512.21[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]-280.47[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]-621.80[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]-774.72[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]-524.67[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]-124.14[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]-130.99[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]-455.56[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]-54.45[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]-15.24[/TD]
[/TR]
</tbody>[/TABLE]


Here is the result I'm looking for:

WORKBOOK C
[TABLE="class: outer_border, width: 150"]
<tbody>[TR]
[TD]ItemCode[/TD]
[TD]InvValue[/TD]
[TD]InvTrans[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]619.42[/TD]
[TD]-586.37[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]3.00[/TD]
[TD]-12.50[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]289.56[/TD]
[TD]-280.47[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]4,503.92[/TD]
[TD]-3879.43[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]153.40[/TD]
[TD]-124.14[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]139.18[/TD]
[TD]-130.99[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]56.88[/TD]
[TD]-54.45[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]794.82[/TD]
[TD]-774.72[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]650.37[/TD]
[TD]-627.66[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]553.13[/TD]
[TD]-932.36[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1,926.35[/TD]
[TD]-1709.22[/TD]
[/TR]
</tbody>[/TABLE]


My first issue is that I need to have Workbook C take the ItemCode values from Workbook A and populate only the unique values. The second issue is that I will need Workbook C to populate those unique values and the SUMIF formulas used to give the totals per ItemCode for InvValue and InvTrans without having to open Workbooks A and B.

I look forward to learning the magic!

Justin

 

[Fazza]

hello

One way is a query in workbook C. Defined by

SELECT C.ItemCode, SUM(C.InvValue) AS [InvValue], SUM(C.InvTrans) AS [InvTrans]
FROM (SELECT ItemCode, InvValue, 0 AS [InvTrans]
FROM `C:\YourPath\A.xlsx`.[Sheet1$]
UNION
SELECT ItemCode, 0, InvTrans
FROM `C:\YourPath\B.xlsx`.[Sheet1$]) C
GROUP BY C.ItemCode

Please change the file path & worksheet names (assumed Sheet1) to suit.
If the source files change, just refresh it (like a pivot table).

Manual set up: ALT-D-D-N & follow wizard, Excel files, etc. If you don't see source data take the option and add 'system tables'. Just get any data you can & at the last step of the wizard take the option to edit in MS Query. Within MS Query via the SQL button replace what you see by the above text. OK to enter & then the 'open door' icon to exit MS Query & set up the query in a worksheet. If you get stuck please google for further info/examples.

This will work in Excel for the last 20 years or so. There will be slightly different implementation available in recent Excel versions to do the same thing. Also you can do it with VBA - either a query table or alternatively ADO.

Another obvious alternative approach (which is almost identical) - that might be simpler or more familiar - is a pivot table.

regards

 

[sandy666]

With PowerQuery (Get&Transform) and PivotTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sum of Value[/td][td=bgcolor:#DDEBF7]Attribute[/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ItemCode[/td][td=bgcolor:#DDEBF7]InvValue[/td][td=bgcolor:#DDEBF7]InvTrans[/td][/tr]

[tr=bgcolor:#FFFFFF][td]A[/td][td]

619.42​

[/td][td]

-586.37​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]B[/td][td]

553.14​

[/td][td]

-932.36​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]C[/td][td]

4503.92​

[/td][td]

-3879.43​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D[/td][td]

1926.35​

[/td][td]

-1709.22​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]E[/td][td]

3​

[/td][td]

-12.5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]F[/td][td]

650.37​

[/td][td]

-627.66​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]G[/td][td]

289.56​

[/td][td]

-280.47​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]H[/td][td]

794.82​

[/td][td]

-774.72​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]I[/td][td]

153.4​

[/td][td]

-124.14​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]J[/td][td]

139.18​

[/td][td]

-130.99​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]K[/td][td]

56.88​

[/td][td]

-54.45​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Z[/td][td][/td][td]

-15.24​

[/td][/tr]
[/table]

[SIZE=1]
//PowerQuery Append1
let
    Source = Table.Combine({Sheet1, #"Sheet1 (2)"}),
    Unpivot = Table.UnpivotOtherColumns(Source, {"ItemCode"}, "Attribute", "Value")
in
    Unpivot[/SIZE]

 

[MontgoJG]

Thank you for the quick response. I have entered the query and adjusted it for the file path and file names, but when I attempt to run the query it give me a error that says:

Compile error:

Expected: Case

It highlights the first 'C' after the initial SELECT. Any ideas on what I might be doing wrong?

hello

One way is a query in workbook C. Defined by

SELECT C.ItemCode, SUM(C.InvValue) AS [InvValue], SUM(C.InvTrans) AS [InvTrans]
FROM (SELECT ItemCode, InvValue, 0 AS [InvTrans]
FROM `C:\YourPath\A.xlsx`.[Sheet1$]
UNION
SELECT ItemCode, 0, InvTrans
FROM `C:\YourPath\B.xlsx`.[Sheet1$]) C
GROUP BY C.ItemCode

Please change the file path & worksheet names (assumed Sheet1) to suit.
If the source files change, just refresh it (like a pivot table).

Manual set up: ALT-D-D-N & follow wizard, Excel files, etc. If you don't see source data take the option and add 'system tables'. Just get any data you can & at the last step of the wizard take the option to edit in MS Query. Within MS Query via the SQL button replace what you see by the above text. OK to enter & then the 'open door' icon to exit MS Query & set up the query in a worksheet. If you get stuck please google for further info/examples.

This will work in Excel for the last 20 years or so. There will be slightly different implementation available in recent Excel versions to do the same thing. Also you can do it with VBA - either a query table or alternatively ADO.

Another obvious alternative approach (which is almost identical) - that might be simpler or more familiar - is a pivot table.

regards

 

[MontgoJG]

My apologies, I realized I wasn't entering the query in the correct place.

I am in the query window now, and when I attempt to run the query it gives me the following message:

SQL Query can't be represented graphically. Continue anyway?

When I click 'OK' it gives me another message:

Could not add the table '(SELECT'.

Thank you for the quick response. I have entered the query and adjusted it for the file path and file names, but when I attempt to run the query it give me a error that says:

Compile error:

Expected: Case

It highlights the first 'C' after the initial SELECT. Any ideas on what I might be doing wrong?

 

[MontgoJG]

Just to clarify, I'm looking for something like this:


SELECT C.ItemCode, SUM(C.InvValue) AS [InvValue], SUM(C.InvTrans) AS [InvTrans]
FROM (SELECT t1.`Item Code` AS [ItemCode], t1.`Inventory Value at Standard` AS [InvValue]
FROM `FilePath\FileName.xlsx`.[Sheet1$] t1
LEFT JOIN
SELECT t2.`Item Number`, t2.`Value` AS [InvTrans], t2.`Transaction Type` AS [TransType]
FROM `FilePath\FileName.xlsx`.[Sheet1$] t2
ON t1.ItemCode = t2.`Item Number`
WHERE t2.TransType = "WIP Issue") C
GROUP BY C.ItemCode


I can't get it to work in the query window, I'm assuming due to some syntax I don't know.

 

[Fazza]

That is a 'LEFT JOIN' that won't work in any database AFAIK.
the guts of the query basically say SELECT two fields FROM t1 LEFT JOIN SELECT three fields FROM t2
Normally syntax is SELECT some fields FROM table

Compare that with the UNION that was suggested.

The SQL shows you want a different result from the original question.

Best to clarify the workbook names, worksheet names, field names, etc. That is, what is posted in the thread matches what you test on: and anyone helping working on the same set up.

PS. I tested on the set up as originally posted & the SQL I posted worked for me.

 

[MontgoJG]

Sorry for the confusion, here are the details:

Workbook A:

Workbook name: Aged Inventory JPL 1904.xlsx
Worksheet name: Sheet1
Field names: Item Code, Inventory Value at Standard

Workbook B:

Workbook name: JEI Transaction Register for Costing JPL Rolling 12 Months 1904.xlsx
Worksheet name: Sheet1
Field names: Item Number, Value


The tables should be left joined on 'Item Code' = 'Item Number' and Workbook B needs to include the clause WHERE 'Transaction Type' = "WIP Issue" (don't need to see the Transaction Type in the output)

Please let me know if you need any other information.