Pivoting Column oriented data into a tabular view

  • Thread starter Thread starter DoctorV
  • Start date Start date
D

DoctorV

On an Excel worksheet i have over 100 columns of data that looks like
this
Col A Col B etc 109 more columns just like this
2 7
IN FL
MI GA
LA
MD
MI
NC
NJ
NY
OH
PA
SC
TN
TX

The numbers 2 7 etc relate to a key field. What I need to do is to
pivot this data to look like this
Col A Col B
2 IN
2 MI
7 FL
7 GA
ETC You get an idea Is there any way with a formula or function to do
this? I have several sheets like this to convert Thanks
 
First, most people won't open attachments--too many threats of malicious code.

Second, your text description was sufficient (well, I think it was sufficient!)
to explain what you wanted. Well, I wondered why In and Mi were the only two
that showed up in the after column next to 2, but I figured that was a typo in
the post. I figured you wanted every value under A1, B1, ... to show up.

I'd use a macro approach.

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iCol As Long
Dim LastCol As Long
Dim myRng As Range
Dim DestCell As Range

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set DestCell = newWks.Range("a1")
For iCol = 1 To LastCol
Set myRng = .Range(.Cells(2, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp))
DestCell.Offset(0, 1).Resize(myRng.Rows.Count, 1).Value _
= myRng.Value
DestCell.Resize(myRng.Rows.Count, 1).Value = .Cells(1, iCol).Value
Set DestCell = DestCell.Offset(myRng.Rows.Count, 0)
Next iCol
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
 
Back
Top