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]

Be sure the references match. I just went along with what was in your sample file, if they've changed then so must the formula. I don't see why you need to vlookup subtotals, the sumifs function does this and handled 10,000 rows just fine (much more might require powerpivot). I did notice that not all lines had an item number so hopefully you can link by the item description instead.