Data to Excel Composite Line Chart

Question

I am having some difficulty in populating a data into a composite line chart

I have the following data weekday(sunday,monday,tuesday...saturday)*hours(0,1,2,3...23)*minutes(0/30)

day hour    minutes unique_counts
sunday      0   0   922
sunday      0   30  1011
sunday      1   0   1239
...
sunday      23  0   737
sunday      23  30  985
monday      0   0   1406
...
monday      23  0   545
monday      23  30  666
...
tuesday     0   0   829
...
tuesday     14  0   3059
...
tuesday     23  30  834
...
wednesday   23  30  874
...
thursday    23  30  839
...
friday      23  30  637
...
saturday    23  30  683

Is it possible to populate this structure to a chart ? As I am doing a lotta manual process such as rearranging the individual weekdays and its respective values into separate columns to create a chart. I have 10 similar sheets to repeat the process. Is there any way to automate the sheets or any generic procedure for this data to generate graph plot ?

X-Axis - hour&minutes (00:00, 00:30, 01:00,...23:00, 23:30)

Y-Axis - unique_counts (x,y,z ....)

individual data line for sunday/monday/tuesday/wednesday/thursday/friday/saturday

P.S: if it is a easy process, please excuse me. I am not much proficient in excel. I spoke with a person who knows more excel than me and his suggestion was to record macro when re-arranging the data into individual columns and apply it for several sheets. As, I need more suggestions I am posting this question here.

Thanks!

Edit[13/08/2014]: I just got a chance to look into pivot table. Created a new time column concatenating the hour/minute fields. But still, have to do some manual process for each sheets to generate the pivot data and chart.

enter image description here

user3514930 · Accepted Answer · 2014-08-13T11:01:46.710

I have changed ...
Adding a column D with the formula:

D2 -> =B2+C2/60

to have the time value. Use the macro:

Dim Sr, e, i As Integer

e = 2
Sr = 1
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
For i = 2 To 9999
    If (Cells(i, 1).Value = "") And (e + 1 = i) Then Exit For
    If Cells(i, 1).Value <> Cells(e, 1).Value Then
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(Sr).XValues = Range("D" & e & ":D" & i - 1)
        ActiveChart.SeriesCollection(Sr).Values = Range("E" & e & ":E" & i - 1)
        ActiveChart.SeriesCollection(Sr).Name = Cells(e, 1).Value
        e = i
        Sr = Sr + 1
    End If
Next

The code generate a multiline chart.
Without adding column:

Dim Sr, e, i, k As Integer
Dim Stri As String

e = 2
Sr = 1
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
For i = 2 To 9999
    If (Cells(i, 1).Value = "") And (e + 1 = i) Then Exit For
    If Cells(i, 1).Value <> Cells(e, 1).Value Then
        Stri = "{"
        For k = e To i - 1
            Stri = Stri & (Cells(k, 2).Value + Cells(k, 3).Value / 60) & ";"
        Next
        Stri = Mid(Stri, 1, Len(Stri) - 1) & "}"
        Stri = Replace(Stri, ",", ".")
        Stri = Replace(Stri, ";", ",")
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(Sr).Formula = "=SERIES(""" & Cells(e, 1).Value & """," & Stri & "," & ActiveSheet.Name & "!" & Range("D" & e & ":D" & i - 1).Address & "," & Sr & ")"
        e = i
        Sr = Sr + 1
    End If
Next

The new code rebuilt the formula for the chart...
Carefully with the second code, because the number are displayer like "," but inserted like ".".
That the reason for the replace function.

Data to Excel Composite Line Chart

1 Answers1