How to transpose 2 row in Excel spreadsheet

  • Thread starter Thread starter nikki
  • Start date Start date
N

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.
 
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
 
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
 
Back
Top