Transpose Crosstab Data in Excel 2000 to flat file for use in MS A

[Syak]

I have data being sent to me in a crosstab format with months as column
headings.

I need to transpose the crosstab data into a flat list to use in a database.

Any easy way to accomplish? If not, anyone happen to have a VBA script I can
modify and leverage?

Thanks in advance.

 

[Nav]

If you simply want to transpose the data, then select the data, copy it and
select paste special. Select the Transpose argument in the dialog box and
paste (this will paste values).

If you want a direct link then use the =transpose("Range") as an array
formulae which you must use Ctrl+Shift+Enter. The area you enter the formula
into should be the smae size as the one you are trying to copy.

Hope this helps.

 

[Dave O]

Just so we're not talking at cross purposes... can you post some sample
data in "before" and "after" format?

 

[Syak]

Current format is:

Jul-06 Aug-2006
East Apples 5,526,415 5,520,890
West Oranges 168,822 4,652
North Peaches 8,555,579 8,886
South Pears 26,515 895,321

What I want is:

East Apples Jul-06 5,526,415
East Apples Aug-06 5,520,890
West Oranges Jul-06 168,822
West Oranges Aug-06 4,652
North Peaches Jul-06 8,555,579
North Peaches Aug-06 8,886
South Pears Jul-06 8,555,579
South Pears Aug-06 895,321

 

[Dave Peterson]

How about a little macro:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet

Dim oRow As Long
Dim HowManyCols As Long

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

FirstCol = 3
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

HowManyCols = LastCol - FirstCol + 1

NewWks.Range("a1").Resize(1, 4).Value _
= Array("Area", "Category", "Date", "Qty")

oRow = 2
For iRow = FirstRow To LastRow
NewWks.Cells(oRow, "A").Resize(HowManyCols, 1).Value _
= .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Resize(HowManyCols, 1).Value _
= .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Resize(HowManyCols, 1).Value _
= Application.Transpose(.Range("c1").Resize(1, HowManyCols))
NewWks.Cells(oRow, "d").Resize(HowManyCols, 1).Value _
= Application.Transpose(.Cells(iRow, "c").Resize(1, HowManyCols))
oRow = oRow + HowManyCols
Next iRow
End With

With NewWks
.Range("c1").EntireColumn.NumberFormat = "mm/dd/yyyy"
.Range("d1").EntireColumn.NumberFormat = "#,##0.00"
.UsedRange.Columns.AutoFit
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm