Arranging Data For Chart

  • Thread starter Thread starter John Gregory
  • Start date Start date
J

John Gregory

Data from a table on a website is copied to a Excel 2000 workbook. Years
down the left column and months across the top. Big matrix. I need to plot
the data month by month for several years. I can't shade the numbers from
line to line like I can if they were all in one column. I either have to
find a way to automate the conversion of this table to one long column in
crono order or find some way to control the curser so I can shade the
numbers from row to row so they appear on the chart as one solid line; not a
separe line for each year with months on the X axis. I need Years on the X
axis (currently on lines in the worksheet) and values on the Y axis.

Please point me in the direction for instructions.
 
Hi John
You should be able to do this with a macro.

Assuming your date years are in column A, your months are in row 1 col
B to M, and your data arranged below, selecting the used range an
running this should work:

Sub col_data()
input_range = Selection.Address
RowNum = Range(input_range).Rows.Count
ColNum = Range(input_range).Columns.Count
Columns(1).Rows(RowNum + 2).Select ' range to transpose to

For rn = 2 To RowNum
For cn = 2 To ColNum
ActiveCell.Value = "1 " & " " & Columns(cn).Rows(1).Value & " "
Columns(1).Rows(rn).Value
ActiveCell.Offset(0, 1).Value = Columns(cn).Rows(rn).Value
ActiveCell.Offset(1, 0).Select
Next cn
Next rn


End Su
 
Uh Oh! Trouble. I get the drift of what you're doing but I done't know where
to start. I went to TOOL/MACRO/RECORD NEW MACRO, assigned a name, then
jumped to the page that contains the date. I was hoping to see a window that
would guide me or at least show what was being written as a macro. When I
saw nothing I went back, stopped the writing of the macro then deleted it. I
figured I don't know what I'm doing yet so I didn't want to be writing codes
that can do harm.

Before I deleted though, I copied what I did so I could use it as a frame of
reference for my question. I entered the Macro mode from the 1st page
(Outline page) of my workbook then clicked to the page that has the data.
That's were I couldn't relate what I saw to what you've givem me here,
Nicky>. Maybe you can help get me started please? Here's what I wrote...
that I'm sure is wrong:

Sub Macro_41()
'
' Macro_41 Macro
' Macro recorded 3/3/2004 by John Gregory
'

'
Range("D19").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Range("B2").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Outline").Select
Range("M18").Select
End Sub

!) I don't think you want me to simply start a macro that takes my cursor
from the outline page to the data page, then copy the code you wrote and
then stop the macro do you?

2) I don't know what to expect for output. Am I going to end up with one
long column?

3) Do I need to make a separate work sheet as the target to receive the
rearraged data?

Sorry to be so green but if I can just get oriented here, I'm sure I can
build the macro.

I really apprecaite your help, Nicky.
 
Hi John,
Maybe the attached will help.

If you select the range that contains the data (A1:M108)
then run the macro (select Tools, Macro, Macros, then run col_data)

it should arrange the dates in a single column from cell A110 down an
the data values in column B110 down, ie adjacent to the appropriat
dates. This can then be used to create a chart.

To see the code in place, select select Tools, Macro, Visual Basi
Editor

If this doesn't work, maybe you coud attach a workbook with the data i
it so we can see how the data are laid ou

Attachment filename: transpose download.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=46338
 
Back
Top