find attached !
Master sheet contains helper column.
formula used here are array formula you need to confirm them by holding control and shift together and then hitting enter instead of just pressing enter.
in L1 of master sheet give a name helper column
in L copy paste below
=COUNTIF($E$2:J2,"<>"&"") and drag down.
in output sheet in a3 copy paste below and then hold control and shift together and then hit enter
=INDEX(Master!A$2:A$7,MIN(IF(Master!$L$2:$L$7>=ROW(A1),ROW(Master!$L$2:$L$7)-1)))
drag down and drag to right up to column D (up to locations Column)
for type which is in column E (in E3 copy paste below and hold control and shift together then hit enter and drag down)
=SUBSTITUTE(INDEX(Master!$A$1:$J$1,MID(TEXT(SMALL(IF(Master!$E$2:$J$7<>"",ROW($E$2:$J$7)*1000+COLUMN($E$2:$J$7)),ROW(A1)),"R000C000"),6,3)+0),"Type ","")
for gross sales which is in column F (In F3 copy paste below and hold control and shift together and then hit enter and drag down)
=INDIRECT("MASTER!"&TEXT(SMALL(IF(Master!$E$2:$J$7<>"",ROW($E$2:$J$7)*1000+COLUMN($E$2:$J$7)),ROW(A1)),"R000C000"),0)
Bookmarks