Help needed in excel transpose

[aswin_aditi]

I need a macro to transpose data in the excel.The data that i have is
in the following format:

Summary
Some data
Steps
1)Some data
2)Some data
Expected Result
Some data
Keywords
Some data


This is repeated in the next row and so on..


I need it in the following format


Summary Steps Expected Result Keywords
somedata 1)Some data Some data Some data
2)Some data
Summary Steps Expected Result Keywords
somedata 1)Some data Some data Some data
2)Some data


Note that the number of steps under the steps column here vary from 1
to 10.


Thanks
Ash

 

[Bob Phillips]

Your example is very poor as you only provide one set of data, and don't say
how the second set of data is associated with its summary etc. For instance,
what would

Summary
Some data
More data
Steps
1)Some data
2)Some data
More data 1)
More data 2)
More data 3)
Expected Result
Some data
More results
Keywords
Some data
More keywords

this look like. We can guess that Some should be grouped and More should be
grouped, but that is a visual assumption, what would be the data rule that
the code could use?



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ash]

Hi Bob

The data is in the following format

Summary
Summary1
Steps
1)Steps1
Expected Result
Expected Result1
Keywords:
Keywords1
Summary
Summary2
Steps
1) Steps2
2) Steps2
3) Steps2
Expected Result
Expected Result2
Keywords:
Keywords2

I need it as :


Summary Steps Expected Result Keywords:
Summary1 1)Step1 Expexted1
Keywords1
Summary2 1)Step2 Expexted2
Keywords2
2)Step2
3)Step2
Summary3 1)Step3 Expexted3
Keywords3



Any help on this will be great.

Thanks
Ash

 

[Ash]

Sorry i need it in the bellow format


I need it as :


Summary Steps Expected Result
Keywords:
Summary1 1)Step1 Expexted1
Keywords1
Summary2 1)Step2 Expexted2
Keywords2
2)Step2
3)Step2
Summary3 1)Step3 Expexted3
Keywords3


Thanks
Ash

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim iResult As Long
Dim iStep As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
Select Case Cells(i, "A").Value
Case "Keywords:"
Cells(i + 1, "A").Copy Cells(i - 1, "B")
Rows(i & ":" & i + 1).Delete
Case "Expected Result"
iResult = i + 1
Case "Steps"
Cells(iResult, "A").Resize(, 2).Copy Cells(i + 1, "B")
Rows(iResult - 1 & ":" & iResult).Delete
Cells(i, "A").Resize(iResult - i - 1).Insert
Shift:=xlToRight
Case "Summary"
Cells(i + 1, "A").Copy Cells(i + 3, "A")
Rows(i & ":" & i + 2).Delete
End Select
Next i
Rows(1).Insert
Range("D1").Value = "Keywords:"
Range("C1").Value = "Expected Result"
Range("B1").Value = "Steps"
Range("A1").Value = "Summary"

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Ash]

thanks bob