Excel Charting - series from column without sorting possible? 1

[shellig]

OK, long subject but I was just asked to take our regular report and graph it differently without sorting.
Our format is:

Date       Run  Batch	Value
9/1/2006	1	1	0.62229978
9/3/2006	2	1	0.477822602
9/3/2006	3	2	0.473598063
9/4/2006	4	1	0.82828949
9/5/2006	5	1	0.346625262
9/6/2006	6	2	0.832642658
9/7/2006	7	2	0.321734769
9/7/2006	8	2	0.403464117
9/7/2006	9	1	0.012714166
9/9/2006	10	2	0.78437798

We typically graph Value by Run. (Run is actually a much more complex number which is why we use a line chart for traceability)
Management wants us to now graph Value by Date with two lines, one for each Batch... but (here is the catch) they do not want to change the order of the data so they can see it in its current format.
Simple solution is create another sheet with a copy of the data and sort and graph but this file is already 1 MB.
Is there any way to dynamically choose the series by the Batch number? This may be a VBA solution or a formula using IF and OFFSET but I do not know where to start!
Thanks for any help

 

[SkipVought]

Hi,

On a separate sheet, I'd use MS Query via Data/Get External Data/New Database Query -- Excel Files -- YOUR WORBOOK -- YOUR SHEET (containing Date, Run, Batch & Value)

Query selecting Date, Batch & SUM Value

Plot query results.



Skip,
[sub]
[red][/red]
[/sub]

 

[shellig]

Thanks Skip but that did not get me what I wanted.
I think I am much closer thanks to a thread on:

http://www.mrexcel.com/archive/VBA/14192.html

where a solution was posted anonymously.
Using that and the data file in the original post saved in an excel spreadsheet titled

Sub Update()

Dim DynamicRange As Range
Dim cell As Range
Dim x As Integer
x = 0
Range("C1:C6000").Select
For Each cell In Selection
If cell.Value = 2 Then
If x = 0 Then
Set DynamicRange = cell.Offset(0, 1)
x = 1
Else
Set DynamicRange = Application.Union(DynamicRange, cell.Offset(0, 1))
End If
End If
Next cell
DynamicRange.Activate
    ActiveWorkbook.Names.Add Name:="bat2y", RefersTo:=DynamicRange

x = 0
Range("C1:C6000").Select
For Each cell In Selection
If cell.Value = 2 Then
If x = 0 Then
Set DynamicRange = cell.Offset(0, -2)
x = 1
Else
Set DynamicRange = Application.Union(DynamicRange, cell.Offset(0, -2))
End If
End If
Next cell
DynamicRange.Activate
    ActiveWorkbook.Names.Add Name:="bat2x", RefersTo:=DynamicRange

x = 0
Range("C1:C6000").Select
For Each cell In Selection
If cell.Value = 1 Then
If x = 0 Then
Set DynamicRange = cell.Offset(0, 1)
x = 1
Else
Set DynamicRange = Application.Union(DynamicRange, cell.Offset(0, 1))
End If
End If
Next cell
DynamicRange.Activate
    ActiveWorkbook.Names.Add Name:="bat1y", RefersTo:=DynamicRange

x = 0
Range("C1:C6000").Select
For Each cell In Selection
If cell.Value = 1 Then
If x = 0 Then
Set DynamicRange = cell.Offset(0, -2)
x = 1
Else
Set DynamicRange = Application.Union(DynamicRange, cell.Offset(0, -2))
End If
End If
Next cell
DynamicRange.Activate
    ActiveWorkbook.Names.Add Name:="bat1x", RefersTo:=DynamicRange

   Charts.Add
    ActiveChart.ChartType = xlXYScatterLines
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("F10"), PlotBy:= _
        xlColumns
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=testfile.xls!bat1x"
    ActiveChart.SeriesCollection(1).Values = "=testfile.xls!bat1y"
    ActiveChart.SeriesCollection(1).Name = "=""1"""
    ActiveChart.SeriesCollection(2).XValues = "=testfile.xls!bat2x"
    ActiveChart.SeriesCollection(2).Values = "=testfile.xls!bat2y"
    ActiveChart.SeriesCollection(2).Name = "=""2"""
    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Value"
    End With
End Sub

works...so I thought I would share! (Very proud of myself)

I still have a problem when I try to run this because the defined name field can only hold so many characters (1024 I believe) and with a lot of records (as in my case) you run out of room. Perhaps I can break that up and add them together in the series but have not completely resolved it yet.

Any ideas on how to improve or solve my issue is appreciated! (This could have probably been done with some loops and made shorter but I am not sure how right now

 

[shellig]

Another solution that my co-worker reminded me of which I ended up doing just now was creating a column E that had the formula in that column starting with E2: =IF($C2=1,D2,#N/A) and a column F starting with F2: =IF($C2=2,D2,#N/A) and copying these formulas down for all the records.
The "#N/A"s are important so those points won't graph and you can still get a line. Creating the two columns gives me the ability to graph those against the date column (A) and get what I want.
It does add more data to an already bloated file but is one way to quickly get what I was looking for.
Thanks and I hope this helps others!

 

[SkipVought]

It works!

TRANSFORM Sum(`Sheet1$`.Value) AS 'Sum of Value'

SELECT `Sheet1$`.Date

FROM `D:\My Documents\vba\query stuff A`.`Sheet1$` `Sheet1$`

GROUP BY `Sheet1$`.Date, `Sheet1$`.Batch

Pivot `Sheet1$`.Batch

Results:
[tt]
Date 1 2
09/01/06 0.622
09/03/06 0.478
09/03/06 0.474
09/04/06 0.828
09/05/06 0.347
09/06/06 0.833
09/07/06 0.013
09/07/06 0.725
09/09/06 0.784
[/tt]
no code other than the SQL PIVOT.

Skip,
[sub]
[red][/red]
[/sub]

 

[shellig]

Reply...yep that works too.
I was hoping to have the solution in the same workbook without adding more data/bulk but this may be a better solution.
If anyone tries this solution by SkipVought and wants the lines to be complete, don't forget to go to Tools>Options>Chart and check the Interpolated choice.
Thanks!