Converting Crosstab into Tabular Format

I am in need of converting a "Crosstab" style worksheet into a "tabular" format. Basically, I have values that are spread across columns and I want them down the side with the headers repeating themselves.

My thoughts on the mechanics are it would do the following:
>Insert a column to the left of the data.
>Count the number of populated rows in the data set (this will change)
>Using this count, loop through each column and copy and paste the values down the 1st few rows...all the while populating the former header column down the side.

I am uploading a "before" and "after" look to give an idea of what I need it to do.


Does this make sense? Can anyone help with the vba?

Thanks! This site is the best.

Cheers!

James

Originally Posted by jpendegraft

I am in need of converting a "Crosstab" style worksheet into a "tabular" format. Basically, I have values that are spread across columns and I want them down the side with the headers repeating themselves.

My thoughts on the mechanics are it would do the following:
>Insert a column to the left of the data.
>Count the number of populated rows in the data set (this will change)
>Using this count, loop through each column and copy and paste the values down the 1st few rows...all the while populating the former header column down the side.

I am uploading a "before" and "after" look to give an idea of what I need it to do.


Does this make sense? Can anyone help with the vba?

Thanks! This site is the best.

Cheers!

James

tada!

Sub sortnames()
Dim i As Long
Dim j As Long
Dim LastRow As Long
Dim LastCol As Long
Dim aVal() As Variant

LastRow = Cells.SpecialCells(xlLastCell).Row
LastCol = Cells.SpecialCells(xlLastCell).Column

ReDim aVal(LastRow, LastCol) As Variant

For i = 1 To LastRow
For j = 1 To LastCol
aVal(i, j) = Cells(i, j).Value
Next j
Next i

Worksheets.Add

For i = 2 To LastRow
For j = 2 To LastCol
Cells((LastRow - 1) * (j - 2) + i, 1) = aVal(1, j)
Cells((LastRow - 1) * (j - 2) + i, 2) = aVal(i, 1)
Cells((LastRow - 1) * (j - 2) + i, 3) = aVal(i, j)
Next j
Next i
End Sub

Thanks so much for the help.

Another question for you...How would it change this code if I had 2 columns that needed to be copied rather than just the 1st?

I have attached another example.

That code is amazing. Thanks again

Originally Posted by jpendegraft

Thanks so much for the help.

Another question for you...How would it change this code if I had 2 columns that needed to be copied rather than just the 1st?

I have attached another example.

That code is amazing. Thanks again

try this:

Please Login or Register  to view this content.