Excel Transpose / Reference Problem

[Kevin Arst]

Hello -

I am trying to solve a transposition / reference
problem. Please assume I have the following data in a
worksheet such that commas represent new columns.

A,1,2,3,4
B,3,4,5,6

I would like to display the data as follow:

A,1
A,2
A,3
A,4
B,3
B,4
B,5
B,6

Is there a simple way to accomplish this? I am dealing
with thousands of rows and coumns and could use some good
insight.

Thanks,
KA

 

[Harlan Grove]

I am trying to solve a transposition / reference
problem. Please assume I have the following data in a
worksheet such that commas represent new columns.

A,1,2,3,4
B,3,4,5,6

I would like to display the data as follow:

A,1
A,2
A,3
A,4
B,3
B,4
B,5
B,6

Is there a simple way to accomplish this? I am dealing
with thousands of rows and coumns and could use some good
insight.

This isn't transpose. It'd be better described as uncrosstabbing. But there is a
way to do it. If your original 2 row by 5 column range were named Tbl, and if
the top-left result cell were G5, you could try

G5:
=OFFSET(Tbl,INT((ROW()-ROW($G$5))/(COLUMNS(Tbl)-1)),0,1,1)

H5:
=OFFSET(Tbl,INT((ROW()-ROW($G$5))/(COLUMNS(Tbl)-1)),
1+MOD(ROW()-ROW($G$5),(COLUMNS(Tbl)-1)),1,1)

Select G5:H5 and fill down as far as needed.

 

[Guest]

Brilliant

-----Original Message-----
...

This isn't transpose. It'd be better described as uncrosstabbing. But there is a
way to do it. If your original 2 row by 5 column range were named Tbl, and if
the top-left result cell were G5, you could try

G5:
=OFFSET(Tbl,INT((ROW()-ROW($G$5))/(COLUMNS(Tbl)- 1)),0,1,1)

H5:
=OFFSET(Tbl,INT((ROW()-ROW($G$5))/(COLUMNS(Tbl)-1)),
1+MOD(ROW()-ROW($G$5),(COLUMNS(Tbl)-1)),1,1)

Select G5:H5 and fill down as far as needed.

 

[Gord Dibben]

Kevin

Sub colrows()
'move data from multiple cols to 2 cols
'aaa 123 456 789
'bbb 321 654 987
'to aaa 123
' aaa 456
' aaa 789
Dim SrcRg As Range
Dim DestCell1 As Range
Dim RowCounter As Long, ColOffset As Integer
Dim CurrCell As Range
'pre-select range of cells first
Application.ScreenUpdating = False
Set SrcRg = Selection.Columns(1)
Sheets.Add
Set DestCell1 = ActiveCell
For Each CurrCell In SrcRg.Cells
ColOffset = 1
While CurrCell.Offset(0, ColOffset).Value <> ""
DestCell1.Offset(RowCounter).Value = CurrCell.Value
DestCell1.Offset(RowCounter, 1).Value = CurrCell.Offset(0, _
ColOffset).Value
RowCounter = RowCounter + 1
ColOffset = ColOffset + 1
Wend
Next
End Sub

Gord Dibben Excel MVP