formula for transpose

[surya and siva]

I have a scenario in which I need a formula for transposing 2 columns in
which 1st column contains one category (value) and its sub categories are
present in second column. The following will be the clear picture.

Like the following N no of categories, so I need a formula to transpose
based on the category.
E1 25
26
27
28
29
30
E2 31
32
33
34
35

I want the result in the following pattern.
E1 25 26 27 28 29 30
E2 31 32 33 34 35
Thanking you.

 

[Dave Peterson]

I'm not sure you'll be able to do this using formulas--especially since each
category can have a different number of subcategories.

But you could use a macro.

If you want to try, make sure you save your data first--or test against a copy
of the data. This macro destroys the original data.

Option Explicit
Sub testme()
Dim TopCell As Range
Dim BotCell As Range
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With Worksheets("Sheet1")
FirstRow = 1
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

Set TopCell = .Cells(FirstRow, "A")
For iRow = FirstRow To LastRow
If IsEmpty(TopCell.Offset(1, 0).Value) Then
Set BotCell = TopCell.End(xlDown).Offset(-1, 0)
Else
Set BotCell = TopCell
End If
If BotCell.Row > LastRow Then
Set BotCell = .Cells(LastRow, "A")
End If

.Range(TopCell, BotCell).Offset(0, 1).Copy
TopCell.Offset(0, 2).PasteSpecial Transpose:=True

Set TopCell = BotCell.Offset(1, 0)
If TopCell.Row > LastRow Then
Exit For
End If
Next iRow

On Error Resume Next
.Columns(1).Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

.Columns(2).Delete
End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)