QUESTION :
I have an Excel 2016 Spreadsheet of Signs with their part numbers, quantity and wording. I need to sub-total the quantity of each part number (Section A in the attached bitmap). I would like to be able to see just the subtotals of the data, showing the Part#, Qty and Desc of each subtotal. Using the Subtotal function on the Data tab and choosing “Description” as the “for each change in” doesn’t show the Part number (Section B of the bitmap); using “Part#” as the “for each change in” doesn’t show the Description field. Is there an easy way to get Excel to show me both fields next to the Subtotal?
Thanks in advance!
Bill
ANSWER :
In picture ‘B’, put this ‘modified’ index match formula in cell D5444, then drag downwards.
=INDEX(A:A,MATCH(1,INDEX(B5444=B:B)*(C5444=C:C),0,1)-1,0))
Idea :
use the normal index+match, and edit it to load the A5443 value instead of A5444. (add -1)
Hope it solves.