Best way to combine multiple sheets with multiple subtotals with multiple criteria

casexcel

New Member
Joined
Nov 29, 2007
Messages
21
I'm using Excel 2016

I have 2 different worksheets in 1 workbook. First worksheet (Sales) is sales from a POS system - which is already subtotaled By Item / By Profit Center.
Second worksheet (INV Tx) is Inventory from an accounting system - which was NOT subtotaled detailing how many of each item was transferred by day and to each Profit Center.

Constants on both sheets:

Item Number
Item Description
Profit Center

I need to pull Columns "TRX QTY" and "Unit Cost" from the INV Tx worksheet (Sorted by Item number then by Profit Center. Which was NOT subtotaled - so I ran the Subtotal function for the TRX Qty column) over to the Sales worksheet and have them line up with the Item Number and Row for the corresponding Profit Center in worksheet Sales

Worksheet Sales Columns: A = Profit Center B = Item Number C= TRX Qty D = Unit Cost

Row 1 BK = 11643 Need from INV Tx sheet Need from INV Tx sheet
Row 3 LET = 11643 Need from INV Tx sheet Need from INV Tx sheet
Row 4 DASH = 11643 Need from INV Tx sheet Need from INV Tx sheet

Worksheet INV Tx Columns: A = Item Number B= Profit Center C = TRX Qty D = Unit Cost

Rows 2-62 = each daily entry of Item 11643 to BK

Row 63 is Subtotal of TRX Qty 11643 BK 2143 Row 2 (same figure for all rows - not subtotaled)

Rows 5258 - 5277 11643 LET 380 Row 5258 (same figure for all rows - not subtotaled)
Row 5278 is Subtotal of TRX Qty

Can you direct me to a video on how to best combine these 2 worksheets with the criteria above.

Thanks
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Ok I'll post a formula and you can tell me if it works, hang on.........
 
Last edited:
Upvote 0

Excel 2010
ABCGH
1Profit CenterItem DescriptionItem NumberTRX QTYUnit Cost
2STSB CSC Fruit Cup10419,'INV Tx'!$A$2:$A$9996,])]16,'INV Tx'!$A$2:$A$9996,])]7.24
Sales
Cell Formulas
RangeFormula
G2=SUMIFS('INV Tx'!$C$2:$C$9996,'INV Tx'!$B$2:$B$9996,],'INV Tx'!$A$2:$A$9996,])
H2=SUMIFS('INV Tx'!$D$2:$D$9996,'INV Tx'!$B$2:$B$9996,],'INV Tx'!$A$2:$A$9996,])


Do you just want to sum by condition like above (a pivot table can also) or something else?
 
Last edited:
Upvote 0
Do you just want to sum by condition like above (a pivot table can also) or something else?[/QUOTE]


My ultimate goal is to be able to do a pivot table. However, I either need to get the data into 1 spreadsheet (or 2 if it is summarized enough) so I can then do a pivot table.

My problem is the InvTx worksheet. I need that summarized so I only have 1 line with totals by item then by profit center. It has to be done that way because each profit center sells that item. If I do subtotals, how do I do a VLOOKUP for subtotals?

Once I get that, I need a VLOOKUP that will pull the "Subtotals" from the TRX QTY column from the INV Tx sheet by matching the Item Number and Profit Center criteria.

Than I can do the pivot table.

I hope this makes sense. Thanks for all your help.
 
Upvote 0
Excel 2010
ABCGH
1Profit CenterItem DescriptionItem NumberTRX QTYUnit Cost
2STSB CSC Fruit Cup10419167.24
Sales
Cell Formulas
RangeFormula
G2=SUMIFS('INV Tx'!$C$2:$C$9996,'INV Tx'!$B$2:$B$9996,[@[Profit Center]],'INV Tx'!$A$2:$A$9996,[@[Item Number]])
H2=SUMIFS('INV Tx'!$D$2:$D$9996,'INV Tx'!$B$2:$B$9996,[@[Profit Center]],'INV Tx'!$A$2:$A$9996,[@[Item Number]])


Do you just want to sum by condition like above (a pivot table can also) or something else?



Hi,

The formula came back with a "Inconsistent Calculated Column Formula". I pasted it in G2 of the Sales spreadsheet. Hmmmmm
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top