Create graph with multiple categories

[aggarwal18]

Hi all

I am trying to create a scatter plot in excel and I am getting my rear kicked for such a simple procedure. My problem is I have a lot of data that needs to be compiled in a scatter plot and doing it manually will not be an option. My data kind of looks like this

[TABLE="width: 271"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Legend Title[/TD]
[TD] X Value [/TD]
[TD] [/TD]
[TD]Y Value [/TD]
[/TR]
[TR]
[TD]Category 1[/TD]
[TD]x1[/TD]
[TD][/TD]
[TD]y1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x2[/TD]
[TD][/TD]
[TD]y2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x3[/TD]
[TD][/TD]
[TD]y3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x4[/TD]
[TD][/TD]
[TD]y4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x5[/TD]
[TD][/TD]
[TD]y5[/TD]
[/TR]
[TR]
[TD]Category 2[/TD]
[TD]x2,1[/TD]
[TD][/TD]
[TD]y2,1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x2,2[/TD]
[TD][/TD]
[TD]y2,2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x2,3[/TD]
[TD][/TD]
[TD]y2,3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x2,4[/TD]
[TD][/TD]
[TD]y2,4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x2,5[/TD]
[TD][/TD]
[TD]y2,5[/TD]
[/TR]
[TR]
[TD]Category 3[/TD]
[TD]x3,1[/TD]
[TD][/TD]
[TD]y3,1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x3,2[/TD]
[TD][/TD]
[TD]y3,2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x3,3[/TD]
[TD][/TD]
[TD]y3,3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x3,4[/TD]
[TD][/TD]
[TD]y3,4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x3,5[/TD]
[TD][/TD]
[TD]y3,5[/TD]
[/TR]
[TR]
[TD]Category 4[/TD]
[TD]x4,1[/TD]
[TD][/TD]
[TD]y4,1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x4,2[/TD]
[TD][/TD]
[TD]y4,2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x4,3[/TD]
[TD][/TD]
[TD]y4,3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x4,4[/TD]
[TD][/TD]
[TD]y4,4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]x4,5[/TD]
[TD][/TD]
[TD]y4,5[/TD]
[/TR]
</tbody>[/TABLE]

This is just an example. I have files with close to 40 categories and have close to 200-300 files. So doing a manual insertion is not an option. I am trying to make a scatter plot which will make a plot with category 1 as the series title and plot x and y and then superimpose the same graph with a different color as category 2 and x and y and so on. Is the a way to automate the process?

Thanks
Abhishek Aggarwal

 

[pbornemeier]

If your data is set up very much like the above example
- Data start in row2
- New Category names in column A
- X, Y data in B:C
- No blank rows between categories

this code will graph the first 256 Categories

Option Explicit

Sub PlotSeries()

    Dim lLastSeriesNameRow As Long
    Dim lLastSeriesDataRow As Long
    Dim lActiveRow As Long
    Dim lBlockStart As Long
    Dim lBlockEnd As Long
    Dim ser As Series
    Dim sWorksheetName As String
    Dim lSeriesIndex As Long
    
    sWorksheetName = ActiveSheet.Name
    lLastSeriesNameRow = Cells(Rows.Count, 1).End(xlUp).Row
    lLastSeriesDataRow = Cells(Rows.Count, 2).End(xlUp).Row
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterSmooth
    
    With ActiveChart
        For Each ser In .SeriesCollection
            ser.Delete
        Next
        
        lActiveRow = 2
        Do While lActiveRow <= lLastSeriesNameRow
            lSeriesIndex = lSeriesIndex + 1
            lBlockStart = lActiveRow
            lBlockEnd = Cells(lBlockStart, 1).End(xlDown).Row - 1
            If lBlockEnd > lLastSeriesDataRow Then lBlockEnd = lLastSeriesDataRow
            
            .SeriesCollection.NewSeries
            .SeriesCollection(lSeriesIndex).Name = "=" & sWorksheetName & "!$A$" & lBlockStart
            .SeriesCollection(lSeriesIndex).XValues = "=" & sWorksheetName & "!$B$" & lBlockStart & ":$B$" & lBlockEnd
            .SeriesCollection(lSeriesIndex).Values = "=" & sWorksheetName & "!$C$" & lBlockStart & ":$C$" & lBlockEnd
            lActiveRow = lBlockEnd + 1
        Loop
    End With
End Sub