Rearranging and organizing data imbedded in rows

[sretepe]

I have a table of data where each row represents data for a particular year
and month. The problem is the the data for each day of the month is included
in the row with alternating columns of date (day of month) and data (for that
day). I want to be able to rearrange the table quickly so I can sort the
data without losing the day, month and year the data is associated with. Any
helpful thoughts are appreciated. I'm working with Excel 2003.

Below is an pictoral example of how the table is currently organized
189701 1 0.0 2 0.1 3 0.5 4 0.0 5 3.1 6 3.4
......
189702 1 1.1 3 2.1 3 0.0 5 1.4 5 5.1 7 1.8
......

FYI 189701 = January 1897

 

[Roger Govier]

Hi

I all versions of Excel, provided you select the whole block of data first,
then the row data will remain intact as you perform a sort.

I guess your data is 63 columns wide, and up to 112 rows deep.
If you select all of this first, then Data>Sort>choose column
required>choose Ascending or Descending>OK

As you have Excel 2003, you could place your cursor in any cell of your data
and choose Data>List>Create List>OK
If you don't have headers, the process will insert a new Row1 with headings
of Column1, Column2 etc.
The whole block of data will be enclosed within a blue line.
There will be dropdowns created on each header, and there are options to
Sort ascending or descending on each dropdown (in addition to options to
filter the data).
Choose whichever column you like and sort, and all the data will be sorted
by that column.
Much easier that having to go through the Data>Sort routine each time.

For safety's sake, work on a copy of your data, in case you make an error

 

[sretepe]

Hi,

Thanks for the info on lists. However, my problem in rearranging my data so
I can use the list and sort features. More specifically, I want to convert
the existing spreadsheet so I can sort and perform frequency analysis on the
data. For example:

My current speadsheet looks like this:
189701 1 0.0 2 0.1 3 0.5 4 0.0 5 3.1 6 3.4
189702 1 1.1 3 2.1 3 0.0 5 1.4 5 5.1 7 1.8

I believe I need it to look like the following if I am to perform a
frequency analysis on the entire data without loosing the yr/month and day
info associated with each data point:
YrMo Day Data
189701 1 0.0
189701 2 0.1
189701 3 0.5
189701 4 0.0
189701 5 3.1 ... and so on

I could just wipe out the columns for yr/month and day and transpose all the
row data points to one column and perform the analysis operations. However,
then I loose all the background information (year/month and day). The
spreadsheet is huge so I don't want to rearrange the table step by individual
step.

Thanks,

Erik

 

[Roger Govier]

Hi

The following macro will move the data to another sheet and set it out in
the format you require.
Change the sheet names in the code to match the names for your source data
and the sheet where you wish the resulting data to be placed

Sub MoveData()

Dim lr As Long, i As Long, j As Long, k As Long
Dim wss As Worksheet, wsd As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wss = ThisWorkbook.Sheets("Sheet1")
Set wsd = ThisWorkbook.Sheets("Sheet2")
lr = wss.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

k = 1
For i = 1 To lr

For j = 2 To 32
If wss.Cells(i, j) <> "" Then
wsd.Cells(k, 1) = wss.Cells(i, 1)
wsd.Cells(k, 2) = wss.Cells(i, j)
wsd.Cells(k, 3) = wss.Cells(i, j + 1)
k = k + 1: j = j + 1
End If
Next j

Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

To use the code, copy the code as above
Pres Alt+F11 to invoke the VBE Editor
Insert>Module and Paste the code into the white Pane that appears.
Alf+F11 to return to Excel
Alt+F8 (or Tools>Macros)>highlight MoveData>Run

 

[Dearoledad]

Roger, nice work. I am totally lost about what you wrote but believe it
works. I have a more simple but similar problem. I have a single column with
about 3000 rows and I need move every other cell to the next column and up
one row. Can you help?

Hi

The following macro will move the data to another sheet and set it out in
the format you require.
Change the sheet names in the code to match the names for your source data
and the sheet where you wish the resulting data to be placed

Sub MoveData()

Dim lr As Long, i As Long, j As Long, k As Long
Dim wss As Worksheet, wsd As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wss = ThisWorkbook.Sheets("Sheet1")
Set wsd = ThisWorkbook.Sheets("Sheet2")
lr = wss.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

k = 1
For i = 1 To lr

For j = 2 To 32
If wss.Cells(i, j) <> "" Then
wsd.Cells(k, 1) = wss.Cells(i, 1)
wsd.Cells(k, 2) = wss.Cells(i, j)
wsd.Cells(k, 3) = wss.Cells(i, j + 1)
k = k + 1: j = j + 1
End If
Next j

Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

To use the code, copy the code as above
Pres Alt+F11 to invoke the VBE Editor
Insert>Module and Paste the code into the white Pane that appears.
Alf+F11 to return to Excel
Alt+F8 (or Tools>Macros)>highlight MoveData>Run
--
Regards
Roger Govier

Hi,

Thanks for the info on lists. However, my problem in rearranging my data
so
I can use the list and sort features. More specifically, I want to
convert
the existing spreadsheet so I can sort and perform frequency analysis on
the
data. For example:

My current speadsheet looks like this:
189701 1 0.0 2 0.1 3 0.5 4 0.0 5 3.1 6
3.4
189702 1 1.1 3 2.1 3 0.0 5 1.4 5 5.1 7
1.8

I believe I need it to look like the following if I am to perform a
frequency analysis on the entire data without loosing the yr/month and day
info associated with each data point:
YrMo Day Data
189701 1 0.0
189701 2 0.1
189701 3 0.5
189701 4 0.0
189701 5 3.1 ... and so on

I could just wipe out the columns for yr/month and day and transpose all
the
row data points to one column and perform the analysis operations.
However,
then I loose all the background information (year/month and day). The
spreadsheet is huge so I don't want to rearrange the table step by
individual
step.

Thanks,

Erik