About Us

SmartBlond

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Count all rows in column with data, Except rows 1-5	Gregory Day	Excel Worksheet Functions	4	March 27th 08 02:58 PM
Column Data to Rows	Ken	Excel Discussion (Misc queries)	1	December 5th 07 10:47 PM
Formatting Rows of Data based on Column Data	mwmasch	Excel Worksheet Functions	3	July 24th 07 04:36 PM
Loading Column Data with blank Rows into Data Validation Box	ExcelMonkey	Excel Worksheet Functions	3	October 13th 05 06:09 PM
find rows for unique data in 1 column and different data in other.	Dot Majewski	Excel Discussion (Misc queries)	1	January 21st 05 12:23 AM

Menu

About Us