Hi,

This seems like it may be a simple thing, yet I can not for the life of
me figure out how to do this (or at least semi-easily with VBing it for
a while).

I have a simple table filled with the following information (example
for simplicity):

12pm 5pm 10pm
11/20 5 4 7
11/21 5 4 7
11/23 5 4 7
11/24 5 4 7
11/25 5 4 7

So basically I am keeping track of a numeric value three times a day. I
would like to make a chart of it with one series with one long line for
the whole month tracking those numeric values. The chart would have
x-axis as date/time and y-axis with values(1-9).

So there needs to be 3 entries for every day on the x-axis tracking all
of these numbers. I have juggled different designs all over the place
and can not get this to work. Any help would be appreciated.

Thanks,
Marc

Hi Marc,
I think you might have to reorganise your data table if you want 1 line
on your graph.
The left column of Date/Time values could be achieved by formatting it
as Customd/m h:mm AM/PM, then, going down the column...

1/11 12:00 PM
1/11 5:00 PM
1/11 10:00 PM
= A2 + 1 'assuming 1/11 12: PM is in A2

Then select the cell with "= A2 + 1" and fill down to get the rest of
the Date/Time values.

Then by what ever method needed copy the Y values down the column next
to the Date/time values column.

Then insert an X-Y Scatter Chart.

Ken Johnson

Thanks Ken,

That worked very well.

However then I hit a snag in phase two of transposing these cells. I
need a way to increment a cell by going from A2 to A3 to A4 etc by just
adding one to the cell before it. Here's the problem. Since I'm putting
all the rows into one column, I need it to go:

A1
B1
C1
A2
B2
C2

I have way too many values to do manually, so I need some type of
equation to do it. Normally a cut/paste would work here. However if you
just cut/paste the first 3 rows, it will skip down three, so you end up
with:

A1
B1
C1
A5
B5
C5

Obviously that wont work. So I need a way to use the cell position as a
variable and just add one to it. For example:

A1 + 1 = A2
B1 + 1 = B2
C1 + 1 = C1

Something simple like that which will repeat all the way down. But
obviously doing it this way it will add the value in the cell, not the
cell position itself. I've tried many different ways to do this, and no
go. Any ideas on how to increment a cell to it's next row position?

Marc

Hi Marc,
I thought that might be a problem.
I was working on it last night.
I'll get back to you.
Meanwhile, it might help if you tell me the cell addresses of your data
as it stands. In particular, what is the address of the very first 12
pm reading?
How many rows of data?

Ken Johnson

Hi Marc,
try this:

1. Copy your data (values only, not headings) Paste it to a free part
of your worksheet.

2. In the empty column immediately to the left of your pasted data fill
down this series 1,2,3,4,5,.... down to the last row of data. Quickest
way is type 1 in the cell that is in the same row as your first pasted
data value, then 2 in the next cell down, then select both of those
cells, then use the fill handle (little cross that appear when the
cursor is over the bottom-right corner of the selection) to drag down
to the bottom of that column. You could also double click when the
little cross appears and it will fill down to the bottom of the sheet.

3. Select this new column and all of your pasted data. Click in the
Name box on the left side of the formula bar, type DATA then press
enter. DATA is a named range that will be used by the following VLOOKUP
formula

4.Type the following formula into an empty cell that is in row 3 and
has only blank cells below it:

=VLOOKUP(INT(ROW()/3),DATA,MOD(ROW(),3) + 2, FALSE)

Then fill that formula down till it stops showing your data in its
transposed state.

5.Copy and Paste SpecialValues the transposed data to where you
finally want it.

Let me know how it goes.

Ken Johnson

Hi Marc,

Better still try this macro

Option Explicit


Public Sub Table_to_Column()
Dim rngTable As Range
Dim vaTableArray As Variant
Dim vaColumnArray() As Variant
Dim iRowCounter As Long, iColumnCounter1 As Long, iColumnCounter2 As
Long

Set rngTable = Application.InputBox(prompt:="Select the Table of Data",
Type:=8)

If rngTable.Rows.Count * rngTable.Columns.Count
ActiveSheet.Rows.Count - 1 Then
MsgBox "Column would not fit on sheet!"
Exit Sub
End If

vaTableArray = rngTable

For iRowCounter = 1 To UBound(vaTableArray, 1)
For iColumnCounter1 = 1 To UBound(vaTableArray, 2)
iColumnCounter2 = iColumnCounter2 + 1
ReDim Preserve vaColumnArray(iColumnCounter2)
vaColumnArray(iColumnCounter2) = vaTableArray(iRowCounter,
iColumnCounter1)
Next iColumnCounter1
Next iRowCounter

'Find first blank column
iColumnCounter1 = 0
Do
iColumnCounter1 = iColumnCounter1 + 1
If Application.CountA(Cells(1, iColumnCounter1).EntireColumn) = 0
Then
With Cells(1, iColumnCounter1)
.Value = "Transposed Data"
.EntireColumn.AutoFit
End With
For iColumnCounter2 = 1 To UBound(vaColumnArray)
Let Cells(iColumnCounter2 + 1, iColumnCounter1) =
vaColumnArray(iColumnCounter2)
Next iColumnCounter2
Exit Do
End If
Loop

End Sub

Look in your email for a copy of a workbook called Transpose (If the
email works)

Ken Johnson

Hi Marc,
I can't get the email to work with the attachment. Beats me!
Ken Johnson