Line Chart

[Shirley_Burke]

Hi,

I have 20000+ records in a table, and I want to produce a line chart from the following columns:

1) Number: decimal values
2) Date: can have duplicates
3) ItemGroup: can have duplicates

The line chart should show the evolution of the Number for each ItemGroup over time (by Date).

I am thinking, graphically, the line chart should have Number on the x-axis, Date on the y-axis, and each ItemGroup represented as a line on the chart. Is this the right way?

If yes, how can I create this graph? I am not a frequent user of Excel and can't get it to work.

Thank you!

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I am not sure if it the "right way" or not but that is a type of graph that Excel does not do easily, as far as I can see.

The solutions I found involve splitting out the groups into separate columns in some way then you plot all the column pairs.
That sounded a bit tedious so I have written a macro that may help. It makes various assumptions:
1. The worksheet is called Sheet1
2. You want an embedded chart not a separate chart sheet.
3. Numbers are in column A
4. Dates are in column B.
5. Groups are in column C.
6. The data points are in order with all the values for one group all together.
7. Headings are in row 1 and the data starts in row 2.
8. You are using Windows.

Those assumptions can all be changed except number 6. It may be necessary to pre-sort the data to comply with item 6.
If you are using a Mac then a bit of a re-write will be required.

Sub GroupPlot()
    Const rowStrt As Long = 2
    Dim dic       As Object  ' Scripting Dictionary
    Dim arr       As Variant
    Dim i         As Long
    Dim iRow      As Long
    Dim sRow      As Long
    Dim ws        As Worksheet
    Dim r         As Range
    
    Application.ScreenUpdating = False
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set dic = CreateObject("Scripting.Dictionary")
    
    arr = ws.Range("C1", ws.Cells(ws.Rows.Count, "C").End(xlUp).Offset(1))
    
    sRow = rowStrt
    For iRow = rowStrt To UBound(arr) - 1
        If arr(iRow, 1) <> arr(iRow + 1, 1) Then
            Set dic(arr(iRow, 1)) = Range(ws.Cells(sRow, "A"), ws.Cells(iRow, "A"))
            sRow = iRow + 1
        End If
    Next

    With ThisWorkbook.Charts.Add
        .ChartType = xlXYScatterLines
        .HasTitle = True
        .ChartTitle.Text = "Chart Title"
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop
        For i = 0 To dic.Count - 1
            Set r = dic.items()(i)
            With .SeriesCollection.NewSeries
                .Name = r.Offset(, 2).Resize(1, 1)
                .XValues = r
                .Values = r.Offset(, 1)
            End With
        Next
        .Location Where:=xlLocationAsObject, Name:=ws.Name
    End With

End Sub

The code fetches the data for column C, the Groups.
It loops round the data finding the start and finish rows for each group. The range details are stored in a Dictionary.
Each item in the dictionary is recalled and a separate Chart Series is created for each one.

Note: The Application.ScreenUpdating = False is critical. Without it there appear to be timing issues that cause Excel to crash.

The above code needs to be pasted in to a new standard Module.

Regards,