Transpose loop after blank cell

[johnnyb5]

I'm working in the middle of a macro which copies data from one column based on the product ID# of a different column. If there is just one product ID# then the formula is simple however, in some circumstances there are several product#s that are the same and in that case I need to copy all of those order#s from a different column and transpose the paste next to the total sum cell. The total sum I have worked out without a problem. The copy multiple order#s and paste transpose I can't seem to get. Example:

A..............B..............C.............D.............................E
product.....Order Qty..Order#..... Total Ordered Qty...... Transposed Order#
234..........3..............C33445.....3.............................C33445
345..........2..............C34556
345..........1..............C35446
345..........2..............C36554.....5.............................C34556....35446....C36554
456..........1..............C37223.....1.............................C37223
567..........2..............C38114.....2.............................C38114
678..........4..............C32144.....4.............................C32144

The vba that I'm using here so far is:

'Adds calculation formula and autofills calculation to lastrow
ActiveSheet.Range("A1").Select
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]<>R[1]C[-3], SUM(R2C[-2]:RC[-2])-SUM(R1C:R[-1]C),"""")" (this is the sum formula that works good)
Range("D2").AutoFill Range("D2:D" & Lastrow)
Range("D2:D" & Lastrow).Value = Range("D2:D" & Lastrow).Value
Range("A1").Select

'Copies and transposes the order numbers next to the sum
ActiveSheet.Range("E2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]>0, RC[-2], """")" (this formula copies one order number fine, but I can't make it copy
Range("E2").AutoFill Range("E2:E" & Lastrow) the multiple numbers and transpose like the example shows)
Range("E2:E" & Lastrow).Value = Range("E2:E" & Lastrow).Value

Range("A1").Select
Range("E1").Select

Any help with making this VBA copy the multiple order#s and transpose the paste next to the sum would be great.

Thanks,
JB

 

[johnnyb5]

This will replace the 2nd paragraph of your code:

'Copies and transposes the order numbers next to the sum
ActiveSheet.Range("E2").Select
ActiveCell.Formula = "=IF(A2<>A1,C2,IF((A1=A2)*(C1=C2),E1,E1&"" ""&C2))"
Range("E2").AutoFill Range("E2:E" & Lastrow)
Range("E2:E" & Lastrow).Value = Range("E2:E" & Lastrow).Value
Range("A1").Select
Range("E1").Select