How to transpose 2 row in Excel spreadsheet

[nikki]

Hi all,

Need your help urgently.....pls

I hv a currently worksheet with following format

Part# Prd.Name Code PaymentA Payment B
===== ======== ==== ======== =========
A Apple 001 300 500
B Orange 002 40 75
C Mango 005 1004 600

And I need to transpose them like this format :
Part# Prd.Name Code Payment
===== ======== ==== ========
A Apple 001 300
A Apple 001 500
B Orange 002 40
B Orange 002 75
C Mango 005 1004
C Mango 005 60

Is there a way to do it ? Thanks in advance.

 

[Nicky]

Hi Nikki this is Nicky!

assuming that
a) the lay out is as you've shown, with payment 2 in the 5th column,
b) there is a maximum of 2 payments

then this might work. Select the cell on the left just below the heade
row (eg A2 in the example) and try this macro (save into a tes
workbook, just in case).



Sub replicate_products()
Do While ActiveCell.Value <> ""
If ActiveCell.Offset(0, 4).Value <> "" Then
ActiveCell.Offset(1, 0).EntireRow.Insert
For n = 0 To 2
ActiveCell.Offset(1, n).Value = ActiveCell.Offset(0, n).Value
Next
ActiveCell.Offset(1, 3).Value = ActiveCell.Offset(0, 4).Value
ActiveCell.Offset(0, 4).ClearContents
End If
ActiveCell.Offset(1, 0).Select
Loop
End Su

 

[Nikki]

Hi Nicky this is Nikki,

Thank you vey much. It's work.

 

[Neil]

Initially, use the TRIM function to remove blank spaces
then ensure that you have formatted the cells according to
their content.

Hope it helps,

Any questions feel free to email me