Reorient Table

[Guest]

I have been given (for analysis) a table imported into Excel with two
vairables, but a strange orientation:

"date1,date2,date3, etc." is in the odd numbered rows, and
"n1,n2,n3,etc." is in the cells of the even numbered rows (below) and is the
number of times the event of interest occurred on the dates in the Odd-row
above. This repeats for several jundred rows. Each date-row represents a
week...so there are 7 columns of date-over-number,date-over-number, etc.

Is there a simple way (macro?) to reorient the data into a more manageable
two-row or two-column format?

 

[Bernie Deitrick]

Gary,

Assuming all that you have in the worksheet is the table, select a single
cell in the table and run the macro below to reorient the table into a two
column table.

HTH,
Bernie
MS Excel MVP

Sub GaryReorientTable()
Dim myRange As Range
Dim i As Integer

Set myRange = ActiveCell.CurrentRegion
For i = 1 To myRange.Rows.Count Step 2
myRange.Rows(i).Resize(2).Copy
Cells(65536, myRange(1, 1).Column + 8) _
.End(xlUp)(2).PasteSpecial Transpose:=True
Next i
End Sub