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