RFI: Transposing/moving data in a spreadsheet

[Andyd]

I hope this isn't a stupidly simple question.

I have precipitation data for numerous locations for about the last 50
years. The data is in Excel, and in a single ( very large)
spreadsheet, in the following format

Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 etc. etc. etc.
Loc Year Jan Feb Mar Apr

Where Loc=location

So for Alpine, TX, I will have 50 lines for 1950-1999 with the yearly
data in a single line. I have 1000+ lines in the sheet.

In order to calculate standardized percipitation indices, I need the
data for each location to be in a single column. Ultimately three
columns actually, with Column 1 = Year, Column 2 = Month, and Column 3
= Precip. So for Alpine, I need a single spreadsheet (which will then
be saved as a text file to input into the SPI program) that is 600
lines long, with each row being one month's precip data, for 1950 -
1999.

I am trying to figure out how to move the data from rows, with one row
per year to a single column, without doing repetitive cut and pastes
(which I refuse to do actually). I am only vaguely familiar with
PivotTables, but this doesn't seem to be what I need. Nor does it
seem apprporiate for any of the LOOKUPs, as these seem too simple for
this many lookups. Can't do IFs or anything like that, too much in
there.

And in a similar vein, I have another spreadsheet with precip data for
a single location, only this time it has daily data in a similar
format, except in that there is a single row for each day/date, and a
single column for each month, and a separate worksheet for each year,
the worksheets are titled "2000", "1999", etc. Not important right
now, but when typing up this request I remembered that I have been
scratching my head over this one for a while. I have 70 years of data
and would like to be able to convert it all in one swell foop, so to
peak.

The only thing I have thought of so far is to concatonate together a
"code" to help in a lookup, but I can't figure out how to do the
lookup in multiple columns.

Any help would be appreciated. Like I said, I hope this isn't a stupid
question with a simple answer.

andyd

(e-mail address removed)

 

[Larry McFadden]

If you want to move data from a row in one worksheet("Source") to a column
in another worksheet("Target"), where "Source" and "Target" are the names of
the respective worksheets, the following macro does the job. I used this to
move a health care facility's census data from rows in the source
worksheets to columns in the target worksheets in order to prepare a state
required report. Be sure to place the cursor in the first cell of the
target worksheet's column, then move to the source worksheet and place the
cursor in the first data cell there before running the macro. Of course,
you'll need to edit the macro by replacing "Target" and "Source" with your
actual worksheet names. Then if you need to do the same operation for other
pages, you'll need to edit the macro with the appropriate worksheet name
changes. You can use whatever keyboard shortcut you wish to assign (I used
Ctrl+c). Then while holding the Ctrl key down, depressing the c key
repeatedly will link each cell in the row of the source worksheet to its
appropriate cell in the column of the target sheet, or you can hold both the
Ctrl and the c keys down, and the macro repeatedly runs itself very rapidly.
If doing this overshoots the range of data, you simply go to the target
sheet and delete the unwanted links. Hope this helps. Larry

Sub CensusLink()
'
' CensusLink Macro
'
' Keyboard Shortcut: Ctrl+c
'
Selection.Copy
Sheets("Target").Select
ActiveSheet.Paste Link:=True
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("Source").Select
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub