Another alternative is to create a pivot table with "Step" in the
Columns
selection, "Order" in the
Rows
selection and "Count of Step" in the
Values
selection. On my sample the pivot table is in columns F to K, rows 1 to 5 and looks like this
Count of Step Column Labels
Row Labels Cut Inspect Measure Ship Grand Total
ORD00001 1 1 1 3
ORD00002 1 1 1 1 4
Grand Total 1 2 2 2 7
I then used simple formulae to get the table in the format you describe - on the same sheet my results table is in Range M3:R4 with the following formulas
M3: =F2 (i.e. the Order)
N3: =VLOOKUP(M3,A:B,2,FALSE) (i.e. the Amount)
O3: =IF(G3>0,G$2,"")
i.e. if there is a count in the pivot table, display the header. I dragged O3 across to column R and down to row 4.
Results
ORD00001 100 Inspect Measure Ship
ORD00002 200 Cut Inspect Measure Ship
EDIT:
My bad. I've re-read the question and spotted the "paste the step to the next empty cell to the right". Follow the same steps above, but amend to
O3: =IF(G3>0,G$2,"REMOVE!")
Then highlight your results, Ctrl-F (or Find & Select, Find).
In the dialog box
Find What: REMOVE!
Look In: Values <-- very important!
Click Find All, then Ctrl-A will select them all.
Close the Find Dialog box and hit Ctrl-MinusSign - Select "Shift Left" from the pop-up.
All your values will move left to fill in the gaps thus
ORD00001 100 Inspect Measure Ship
ORD00002 200 Cut Inspect Measure Ship
N.B. This doesn't work with the original formula and Find & Select, Goto Blank (which is the usual technique) because Excel does not recognise the "" as "blank"