Return cells in a row if specific column is populated

[Scott Boser]

The master sheet lists sales people (A2:A7) with employee code, location, then "sale types" in columns E-J. In a different sheet, I need to return each incidence of sale, and return much of that same data as depicted in the Output sheet. I dont know how to have each row populate consecutively when information is present in a given column (sale type).

[Scott Boser]

The original attached had an error in the sale types for what it should look like HELPBOOK2. Sorry

[hemesh]

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)