Column Data to Rows
 

I am trying to switch data from a column format to a row format. This is a
step beyond a basic transpose. Trying to alter the data to make it easier
to load into a table. Any advice?
I want to go from this:
Site 2007 Q1 2007 Q2 2007 Q3 2007 Q4
4 10 20 30 40
47 15 25 35 45

To thi:
Site Time Value
4 2007 Q1 10
4 2007 Q2 20
4 2007 Q3 30
4 2007 Q4 40
47 2007 Q1 15
47 2007 Q2 25
47 2007 Q3 35
47 2007 Q4 45

Column Data to Rows
 

If the data is laid out nicely (always 4 quarters per year), then I'd use some
formulas and a few manual techniques.

I'm assuming that you have headers in Row 1 and the data starts in row 2.

In D2: =IF(MOD(ROW(),4)=2,C3,NA())
In E2: =IF(MOD(ROW(),4)=2,C4,NA())
In F2: =IF(MOD(ROW(),4)=2,C5,NA())

Then select D2:F2 and drag down as far as you need.

Your worksheet will look like:

Site Time Value
4 2007 Q1 10 20 30 40
4 2007 Q2 20 #N/A #N/A #N/A
4 2007 Q3 30 #N/A #N/A #N/A
4 2007 Q4 40 #N/A #N/A #N/A
47 2007 Q1 15 25 35 45
47 2007 Q2 25 #N/A #N/A #N/A
47 2007 Q3 35 #N/A #N/A #N/A
47 2007 Q4 45 #N/A #N/A #N/A

Now select columns D:F
Edit|copy
Edit|Paste special|values

Add some headers to D1:F1 (and fix the header in C1).

Then apply data|filter|autofilter to D1 and show the rows that are #N/A's.
Delete those visible rows
Remove the filter

Delete column B
and you're done.



SmartBlond wrote:

I am trying to switch data from a column format to a row format. This is a
step beyond a basic transpose. Trying to alter the data to make it easier
to load into a table. Any advice?
I want to go from this:
Site 2007 Q1 2007 Q2 2007 Q3 2007 Q4
4 10 20 30 40
47 15 25 35 45

To thi:
Site Time Value
4 2007 Q1 10
4 2007 Q2 20
4 2007 Q3 30
4 2007 Q4 40
47 2007 Q1 15
47 2007 Q2 25
47 2007 Q3 35
47 2007 Q4 45

--

Dave Peterson

Column Data to Rows
 

Thank you for the input,
I am actually trying to go the other way, From columns to rows. My data
will not be consistent. The client will be adding data each quarter.

"Dave Peterson" wrote:

If the data is laid out nicely (always 4 quarters per year), then I'd use some
formulas and a few manual techniques.

I'm assuming that you have headers in Row 1 and the data starts in row 2.

In D2: =IF(MOD(ROW(),4)=2,C3,NA())
In E2: =IF(MOD(ROW(),4)=2,C4,NA())
In F2: =IF(MOD(ROW(),4)=2,C5,NA())

Then select D2:F2 and drag down as far as you need.

Your worksheet will look like:

Site Time Value
4 2007 Q1 10 20 30 40
4 2007 Q2 20 #N/A #N/A #N/A
4 2007 Q3 30 #N/A #N/A #N/A
4 2007 Q4 40 #N/A #N/A #N/A
47 2007 Q1 15 25 35 45
47 2007 Q2 25 #N/A #N/A #N/A
47 2007 Q3 35 #N/A #N/A #N/A
47 2007 Q4 45 #N/A #N/A #N/A

Now select columns D:F
Edit|copy
Edit|Paste special|values

Add some headers to D1:F1 (and fix the header in C1).

Then apply data|filter|autofilter to D1 and show the rows that are #N/A's.
Delete those visible rows
Remove the filter

Delete column B
and you're done.



SmartBlond wrote:

I am trying to switch data from a column format to a row format. This is a
step beyond a basic transpose. Trying to alter the data to make it easier
to load into a table. Any advice?
I want to go from this:
Site 2007 Q1 2007 Q2 2007 Q3 2007 Q4
4 10 20 30 40
47 15 25 35 45

To thi:
Site Time Value
4 2007 Q1 10
4 2007 Q2 20
4 2007 Q3 30
4 2007 Q4 40
47 2007 Q1 15
47 2007 Q2 25
47 2007 Q3 35
47 2007 Q4 45

--

Dave Peterson

Column Data to Rows
 

Sorry.

I'd use a macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long
Dim oRow As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add
NewWks.Range("a1").Resize(1, 3).Value _
= Array("Site", "Time", "Value")

With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
HowMany = .Cells(1, .Columns.Count).End(xlToLeft).Column - 1
oRow = 2
For iRow = FirstRow To LastRow
If HowMany 0 Then
NewWks.Cells(oRow, "A").Resize(HowMany, 1).Value _
= .Cells(iRow, "A").Value
.Cells(1, "B").Resize(1, HowMany).Copy
NewWks.Cells(oRow, "B").PasteSpecial Transpose:=True
.Cells(iRow, "B").Resize(1, HowMany).Copy
NewWks.Cells(oRow, "C").PasteSpecial Transpose:=True
oRow = oRow + HowMany
End If
Next iRow
End With

Application.CutCopyMode = False

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
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.)

SmartBlond wrote:

Thank you for the input,
I am actually trying to go the other way, From columns to rows. My data
will not be consistent. The client will be adding data each quarter.

"Dave Peterson" wrote:

If the data is laid out nicely (always 4 quarters per year), then I'd use some
formulas and a few manual techniques.

I'm assuming that you have headers in Row 1 and the data starts in row 2.

In D2: =IF(MOD(ROW(),4)=2,C3,NA())
In E2: =IF(MOD(ROW(),4)=2,C4,NA())
In F2: =IF(MOD(ROW(),4)=2,C5,NA())

Then select D2:F2 and drag down as far as you need.

Your worksheet will look like:

Site Time Value
4 2007 Q1 10 20 30 40
4 2007 Q2 20 #N/A #N/A #N/A
4 2007 Q3 30 #N/A #N/A #N/A
4 2007 Q4 40 #N/A #N/A #N/A
47 2007 Q1 15 25 35 45
47 2007 Q2 25 #N/A #N/A #N/A
47 2007 Q3 35 #N/A #N/A #N/A
47 2007 Q4 45 #N/A #N/A #N/A

Now select columns D:F
Edit|copy
Edit|Paste special|values

Add some headers to D1:F1 (and fix the header in C1).

Then apply data|filter|autofilter to D1 and show the rows that are #N/A's.
Delete those visible rows
Remove the filter

Delete column B
and you're done.



SmartBlond wrote:

I am trying to switch data from a column format to a row format. This is a
step beyond a basic transpose. Trying to alter the data to make it easier
to load into a table. Any advice?
I want to go from this:
Site 2007 Q1 2007 Q2 2007 Q3 2007 Q4
4 10 20 30 40
47 15 25 35 45

To thi:
Site Time Value
4 2007 Q1 10
4 2007 Q2 20
4 2007 Q3 30
4 2007 Q4 40
47 2007 Q1 15
47 2007 Q2 25
47 2007 Q3 35
47 2007 Q4 45

--

Dave Peterson


--

Dave Peterson

Column Data to Rows
 

Wonderful! Thank you so much.

"Dave Peterson" wrote:

Sorry.

I'd use a macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long
Dim oRow As Long

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add
NewWks.Range("a1").Resize(1, 3).Value _
= Array("Site", "Time", "Value")

With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
HowMany = .Cells(1, .Columns.Count).End(xlToLeft).Column - 1
oRow = 2
For iRow = FirstRow To LastRow
If HowMany 0 Then
NewWks.Cells(oRow, "A").Resize(HowMany, 1).Value _
= .Cells(iRow, "A").Value
.Cells(1, "B").Resize(1, HowMany).Copy
NewWks.Cells(oRow, "B").PasteSpecial Transpose:=True
.Cells(iRow, "B").Resize(1, HowMany).Copy
NewWks.Cells(oRow, "C").PasteSpecial Transpose:=True
oRow = oRow + HowMany
End If
Next iRow
End With

Application.CutCopyMode = False

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
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.)

SmartBlond wrote:

Thank you for the input,
I am actually trying to go the other way, From columns to rows. My data
will not be consistent. The client will be adding data each quarter.

"Dave Peterson" wrote:

If the data is laid out nicely (always 4 quarters per year), then I'd use some
formulas and a few manual techniques.

I'm assuming that you have headers in Row 1 and the data starts in row 2.

In D2: =IF(MOD(ROW(),4)=2,C3,NA())
In E2: =IF(MOD(ROW(),4)=2,C4,NA())
In F2: =IF(MOD(ROW(),4)=2,C5,NA())

Then select D2:F2 and drag down as far as you need.

Your worksheet will look like:

Site Time Value
4 2007 Q1 10 20 30 40
4 2007 Q2 20 #N/A #N/A #N/A
4 2007 Q3 30 #N/A #N/A #N/A
4 2007 Q4 40 #N/A #N/A #N/A
47 2007 Q1 15 25 35 45
47 2007 Q2 25 #N/A #N/A #N/A
47 2007 Q3 35 #N/A #N/A #N/A
47 2007 Q4 45 #N/A #N/A #N/A

Now select columns D:F
Edit|copy
Edit|Paste special|values

Add some headers to D1:F1 (and fix the header in C1).

Then apply data|filter|autofilter to D1 and show the rows that are #N/A's.
Delete those visible rows
Remove the filter

Delete column B
and you're done.



SmartBlond wrote:

I am trying to switch data from a column format to a row format. This is a
step beyond a basic transpose. Trying to alter the data to make it easier
to load into a table. Any advice?
I want to go from this:
Site 2007 Q1 2007 Q2 2007 Q3 2007 Q4
4 10 20 30 40
47 15 25 35 45

To thi:
Site Time Value
4 2007 Q1 10
4 2007 Q2 20
4 2007 Q3 30
4 2007 Q4 40
47 2007 Q1 15
47 2007 Q2 25
47 2007 Q3 35
47 2007 Q4 45

--

Dave Peterson


--

Dave Peterson