Noobie with pivot table

[dotsent]

Hi guys!

I like Excel a lot and have been using it for years, however always got through without pivot tables and relied just on formulas. Should still be the case with the following quest (SUMIF?), however I feel I have to try out a pivot table sooner or later.

I have a somewhat easy objective as I need to do function similar to SUM and GROUP BY in SQL syntax. I'll give an example below in a simplified way. I have 2 tables on different worksheets, but I suppose that doesn't matter?

I was able to set up pivot table for 1 table (i.e INFLOW) fine which summarized quantities for all items correctly. I'm struggling to add data from the other table though (i.e. OUTFLOW), which should obviously have negative values.

Is it possible to put them both together in a single pivot table and summarize them over 2 data sets? And if so, do I need to enter values in OUTFLOW table as negative (i.e. add minus sign to the values)? In the spirit of getting to know pivot tables better, I would not just create 2 separate pivot tables and compare values with INDEX&MATCH formulas + add another formula to SUM them - unless pivot table can do that for me.

Thanks a lot!



INFLOW
Item Quantity [TABLE="width: 200"]
<tbody>[TR]
[TD]Apple
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Cherry
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Orange
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

OUTFLOW

Item Quantity [TABLE="width: 200"]
<tbody>[TR]
[TD]Cherry
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Orange
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Orange
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fazza]

hello

tables on different worksheets are OK

for combining to a single pivot table there are a few choices. I'll assume you want the SUM & GROUP BY but this isn't necessary: so edit the below SQL to take out the SUM & GROUP BY if you prefer.

I'll asssume the source data is in tables with simple (specifically non-dynamic) defined names of INFLOW and OUTFLOW

I've used negative of the quantity for the OUTFLOW

SELECT Item, SUM(Quantity) AS [Quantity]
FROM INFLOW
GROUP BY Item
UNION ALL
SELECT Item, SUM(-Quantity)
FROM OUTFLOW
GROUP BY Item

Or you could combine the datasets before doing the summing,

SELECT Item, SUM(Quantity) AS [Quantity]
FROM (
SELECT Item, Quantity
FROM INFLOW
UNION ALL
SELECT Item, -Quantity
FROM OUTFLOW)
GROUP BY Item

If you wanted to have the Inflow/Outlfow as text in a field, these could be

SELECT 'Inflow' AS [Category], Item, SUM(Quantity) AS [Quantity]
FROM INFLOW
GROUP BY 'Inflow', Item
UNION ALL
SELECT 'Outlfow', Item, SUM(-Quantity)
FROM OUTFLOW
GROUP BY 'Outflow', Item

Or you could combine the datasets before doing the summing,

SELECT Category, Item, SUM(Quantity) AS [Quantity]
FROM (
SELECT 'Inflow' AS [Category], Item, Quantity
FROM INFLOW
UNION ALL
SELECT 'Outflow', Item, -Quantity
FROM OUTFLOW)
GROUP BY Category, Item

Starting point is to save the data file and I'll assume it has the defined names (INFLOW and OUTFLOW). The defined names should be for the headers and data.

In a new file/workbook, ALT-D-P and choose 'external data source' at the first step. Then next, and then get data, Excel files, OK, browse to choose the data file. Continue following the wizard to the end. Then take the option to edit in MS Query. Via the 'SQL' icon edit the SQL to one of the above. OK to enter it and acknowledge (OK) any message about not being able to graphically represent etc. See the dataset and then via the 'open door
icon exit MS Query and choose to make a pivot table (or you could just make a query table, btw). Drag the fields into position for the pivot table. If you like the worksheet containing the completed pivot table can be moved into the source data file.

regards

 

[dotsent]

First of all - big thanks for an extensive reply.

Perhaps my question misled you a little bit when I mentioned SQL. I meant a SQL "SUM & GROUP BY"-type functionality, not that I would be using an actual query. There is NO external data source nor db connection. Data is static in 2 different worksheets (users can manually just add rows).

So the aim for the pivot table is to get data not from database, but from tables already in the same Excel file. I don't think SQL syntax is used in that scenario where data is locally available, also I can't really even see a place for SQL syntax when the data source is the same Excel file? Again, I would need to group and summarize both INFLOW and OUTFLOW tables, with OUTFLOW having negative values. And I hope to do that with the help of a single pivot table.

Does that put this question in a bit different light?

 

[sheetspread]

In SQL you can write an inner join of two summary queries and subtract the fields, or Fazza's union query to include all uncombined values (probably the best solution here). This can be done with Power BI (Power Query) or MS Query in standalone Excel. The pivot table does have data consolidation (not actual joins) and is another option, but less preferable to SQL I'd say.

 

[Fazza]

The easiest (& best?) way to get data from two worksheets in the one workbook is using the approach described.

It is set up via external data source and SQL (to get the UNION). The SQL samples are the sort of approach used: ignore the sample SQL with the SUM & GROUP BY if you like. [But if you don't need the detail in the pivot table, using that approach will consolidate the data en route to the pivot table & can be preferred for efficiency, especially with huge datasets.]

If the data tables are dynamic (you want to handle additional rows of data seamlessly) then instead of using defined names the approach is to use the worksheet names. Just be aware that if this is set up manually (no VBA) & worksheet names change it will cause a problem with the pivot table which will have the original worksheet names hard coded in the SQL. The syntax for a worksheet source is [worksheetname$]

with workhsheet named "Inflow Data" the SQL would be like : SELECT fields FROM [Inflow Data$]

An alias is good for the table name. Such as : SELECT In.Item, In.Quantity FROM [Inflow Data$] In
Case doesn't matter within the SQL, btw

The SQL is required. The negative signs I used against the Outflow data should address the negative values if they need to be reversed from what they are in the source table.

Another approach with changing data rows is to use VBA to set the defined names. So using an appropriate event, maybe worksheet deactivate, like
sub worksheetdeactivate(etc
me.range("A1").currentregion.name = "tblInflow"
end sub

 

[Fazza]

PS. Thanks sheetspread.

Yes, newer versions of Excel have Power BI. It must offer some serious functionality.

I've found using normal pivot tables and appropriate SQL, and often VBA, I can do everything I've ever needed to do - and some has been pretty high powered. This exists in all Excel versions with pivot tables. So for ~ 20+ years? Since the mid 90s?

 

[Fazza]

PS

Regarding the 'external data source'. It is how it is done even when querying the same workbook. I've been doing this stuff for so long I forget to explain it, sorry. I haven't tested in recent versions, but in older versions it was a problem to set it up from within the workbook & so the data file was saved & a new workbook used to create the pivot table. Once created though, it was OK to move the worksheet containing the pivot table into the source data file.

And another thought. If you want to use worksheet names, near the beginning of the wizard when you go to choose data sources there is an 'options' button that allows you to select 'system tables' and after that the worksheet names will be available in the GUI as a data source. Even if not, you can enter them straight into the SQL.

A bit of googling will find examples/tutorials/help/etc