I would like to transform this table into the table below. I know how to get the PF, Name and Prod Name since they will always be in the same location. What I haven't figured out is how to get the Step Type and Step Name since they can be on varying rows within the sheet.

LOCATION: Bank1 EFF DATE: 1-Jan-02
PF/CT/BNAME TS4 Cinnamon Chews (Category: X, Brand: CANDY) STATUS: In Progress
PROD NAME: Cinnamon Chews APPROVERS:
PROJECT: Project 1
Step #1 Step Type: Master Step Name : TS4
Ingr Id Ingr Name Obj Solution Min Max Weight Total %
TS4_1 Ingr1 sub 12.000000 5.000000 12.000000 7.500000
TS4_2 Ingr2 sub 15.000000 8.000000 15.000000 10.760000
Total Step #2 Step Type: Mix Step Name : TS4_MIX
Ingr Id Ingr Name Obj Solution Min Max Weight Total %
TS4_5 Ingr5 sub 28.000000 28.000000 32.000000 10.846877
TS4_6 Ingr6 sub 72.000000 68.000000 72.000000 27.891968
Total Step #3 Step Type: Blend Step Name : TS4_BLEND
Ingr Id Ingr Name Obj Solution Min Max Weight Total %
9888 Ingr9 ing 0.840000 0.840000 0.840000 0.514594
9999 Ingr4 ing 95.410000 58.449268
TS4_3 Ingr3 sub 3.750000 3.750000 3.750000 2.297293


Into this:
PF Prod Name BNAME Step Ingr Id Ingr Name Min Max Solution Total %
TS4 Cinnamon Chews CANDY TS4 TS4_1 Ingr1 5.000000 12.000000 12.000000 7.500000
TS4 Cinnamon Chews CANDY TS4 TS4_2 Ingr2 8.000000 15.000000 15.000000 10.760000
TS4 Cinnamon Chews CANDY MIX TS4_5 Ingr5 28.000000 32.000000 28.000000 10.846877
TS4 Cinnamon Chews CANDY MIX TS4_6 Ingr6 68.000000 72.000000 72.000000 27.891968
TS4 Cinnamon Chews CANDY BLEND 9888 Ingr9 0.840000 0.840000 0.840000 0.514594
TS4 Cinnamon Chews CANDY BLEND 9999 Ingr4 95.410000 58.449268
TS4 Cinnamon Chews CANDY BLEND TS4_3 Ingr3 3.750000 3.750000 3.750000 2.297293