Re: Rearranging and organizing data imbedded in rows
- From: Dearoledad <Dearoledad@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 28 Aug 2009 07:20:01 -0700
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?
"Roger Govier" wrote:
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
"sretepe" <sretepe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7B4079B0-9FB3-4E45-8AB7-A481616BD395@xxxxxxxxxxxxxxxx
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" wrote:
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
--
Regards
Roger Govier
"sretepe" <sretepe@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4F451066-8256-499F-971A-0C8AC4B333DA@xxxxxxxxxxxxxxxx
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
- Prev by Date: Re: Formatting a cell to divide by 12
- Next by Date: RE: showing zero in cell
- Previous by thread: custom function with conditional formatting?
- Next by thread: Is there a way to put worksheets in alphabetical sequence?
- Index(es):
Relevant Pages
|
