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

[casexcel]

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

 

[sheetspread]

Can you write a vlookup formula?

 

[Forever_EX]

I agree with sheetspread and you can use "Concatenate" to put multiple values into one cell

 

[casexcel]

Can you write a vlookup formula?

I have watched at least 50 videos on VLOOKUP. Not one covers the criteria I need. If I could write a vlookup, I wouldn't be here.

 

[sheetspread]

Can you post some sample data (or a link to a file)?

 

[casexcel]

Link: https://docs.google.com/spreadsheets/d/1r1sYPslOIRUOSuEFaDG0sqHQnuyyTYgFgs736GcTuxc/edit?usp=sharing

 

[sheetspread]

Ok I'll post a formula and you can tell me if it works, hang on.........

 

[sheetspread]

Excel 2010
	A	B	C	G	H
1	Profit Center	Item Description	Item Number	TRX QTY	Unit Cost
2	ST	SB CSC Fruit Cup	10419	,'INV Tx'!$A$2:$A$9996,])]16	,'INV Tx'!$A$2:$A$9996,])]7.24
Sales

Cell Formulas
Range	Formula
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?

 

[casexcel]

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.

 

[casexcel]

Excel 2010
	A	B	C	G	H
1	Profit Center	Item Description	Item Number	TRX QTY	Unit Cost
2	ST	SB CSC Fruit Cup	10419	16	7.24
Sales

Cell Formulas
Range	Formula
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