?
Solved

Excel Graph on change to data

Posted on 2011-03-03
11
Medium Priority
?
301 Views
Last Modified: 2012-05-11
Attached is a spreadsheet of items that I monitored on a server that was recorded every 15 minutes.

I would like to produce a line graph for each column separately using the column 'snapshot' and show a separate line graph of each column individually from column 'B' to column 'AH' as separate charts.

So Chart1 would be Column A ('x' axis) and Column B ('y' axis), Chart2 would beColumn A ('x' axis) and Column C ('y' axis), Chart3 would be Column A ('x' axis) and and Column D ('y' axis),  etc.

My problem is that I only want the chart to show an entry when there is a change to the value in the non-Column A column. In this way rather than show 875 Column 'A' items on the 'x' axis, there would be only a handful, but the 'y' axis would show the entire range of numbers for that particular column.

Thanks in advance for your assistance!

Lenny
TestGraph.xls
0
Comment
Question by:LennyGray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35028720
No Problem.  The challenge, here, is to filter the dataset such that only unique records are shown in Column B, or Column C, or Column D.  Once you have graphs setup for A,B and A,C and A,D, just do the unique autofilter.

In the attached, I have created the three charts, and put them on their own separate tab.  Then, I modified the code behind each tab.  On the event Worksheet.Activate(), I call the simple, one-line macro, to autofilter in place the data, on unique records for the column in question.

For the first chart, that commandline would look like this:

Sub FilterColumnB()

    Sheets("TestGraph").Columns("B:B").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
End Sub

And in the Chart's codepage, the following makes that macro work:

Private Sub Chart_Activate()
    Call FilterColumnB
End Sub

.......

You can also do this manually - but its a few steps.  Select your data, go to the DATA ribbon, find Sort & Filter, select Autofilter - just select the Column range in question (or the entire column) as the list range, then hit the checkbox for unique records only.  If your chart is setup to plot the result of column A and the other column (in this example, column B), then it would only show the changing B data, with corresponding A data in the X-axis.

See attached.

Enjoy!

Dave
TestGraph-r1.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35065791
Does this solution work for you?  Please advise any issues or close as appropriate.

Cheers,

Dave
0
 
LVL 10

Author Closing Comment

by:LennyGray
ID: 35109300
Excellent solution, Dave. I apologize for not responding faster. I was on vacation.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 10

Author Comment

by:LennyGray
ID: 35189323
Hi Dave,

I am not an Excel guru. How do I include the other worksheets so that they do scatter charts?

I apologize for bothering you but I tried my best before writing to you.

Lenny
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35191627
What other worksheets?

If you have a worksheet similar to the "TestGraph" worksheet, with similar layout, just put the code in Module1 into that workbook with that worksheet, change the code:
Sub FilterColumnB()

    Sheets("TestGraph").Columns("B:B").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
End Sub
Sub FilterColumnC()

    Sheets("TestGraph").Columns("C:C").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
End Sub

Sub FilterColumnD()

    Sheets("TestGraph").Columns("D:D").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
End Sub

Open in new window


such that "TestGraph" is the name of that datasheet - and the column references are the columns to get to unique sorting.

then, create a chart with the data and embed the code:
Private Sub Chart_Activate()
    Call FilterColumnB
End Sub

Open in new window

such that the right column is being filtered.

if you're still having problems, send me your work in progress and I'll point out what to do.

Dave
0
 
LVL 10

Author Comment

by:LennyGray
ID: 35192561
Hi Dave-

Attached is what I have done already, which is really nothing more that the additional function calls to column AH - and i forgot how I did that. <sigh> But when I got there, I simp;ly did a copy and paste.

If you could tell me the steps that I should do, I can mimic what you have done. Getting to the code and  the events using a chart selection is a mystery to me. Soemtimes I see the functions and sometimes I see the events. But it is trial and error and I am unsure how I got to these areas. <blush>

Thanks for your gracious offer to help.

Lenny
TestGraph-r1.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35192653
Ok - I'm headed to lunch and will try to respond this afternoon.

Let me give you a few tips, now...

To get to the VBA Developer area, hit ALT-F11

You should see on the left side (or hit CTRL-R - or View Project Explorer) a diagram of your workbook.  There should be Microsoft Excel Objects (containing Sheets or Charts - charts that aren't embedded in a worksheet), and there should be Modules (and you can add modules, which is just adding public code to the workbook).

The code that makes the chart update based on activating a chart tab is embedded in the Microsoft Excel Objects - Chart1, Chart2, etc.

The code that could be called based on events, like activating, are called from this area, but can be stored in the MODULES - and that's where you find where the work gets done.

Click around and see if you find this stuff.

When you create a new chart tab, right click your mouse on the chart tab and select VIEW CODE - that will open the VBA Project area for coding VBA.  It will be blank on the right, you can select one of the chart tabs on the left, grab that code, then select your new chart tab code page and paste it in.

Play with that a bit.

I'll look at your file and comment.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35192677
if you're starting from scratch, again right click on the chart tab, hit VIEW code.  ON the right side, where code goes, you can see the pull down menus. on the left side you'll see (General) - you can change that to Chart - and the right side you can now pull down different events for the chart.

Sorry - off to lunch or I would be pasting pictures :)

Dave
0
 
LVL 10

Author Comment

by:LennyGray
ID: 35193281
Hi Dave -

My day is over. Thanks for the tips. I hope that your lunch was enjoyable.

We'll chat tomorrow. have a good day....and thanks!

Lenny
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35194821
Ok - I made some changes to simplify.

1.  Create a new chart, based on a key column you're plotting.
2.  Right click on the chart tab and click "VIEW CODE"
3.  Copy this and paste it in the sheet codepage:


In the Chart codepage, where you want to make it refresh to a new column you've specified
Private Sub Chart_Activate()

    Call FilterColumn("TestGraph", "D:D")
    
End Sub

Open in new window


4.  Change the Column from "D:D" to the column you're interesting in plotting.  Change your chart settings to your preferences, etc.  Anytime you select this chart tab, the data will refresh as its been setup for this question.

To make this happen, I wrote the following in the main MODULE1 public codepage:
 
Sub FilterColumn(sWS As String, cols As String)
Dim WS As Worksheet
    
    Set WS = ThisWorkbook.Sheets(sWS)
    WS.Columns(cols).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    
End Sub

Sub FilterColumnsClear(sWS As String)
Dim WS As Worksheet
    
    Set WS = ThisWorkbook.Sheets(sWS)
    WS.Columns.AdvancedFilter Action:=xlFilterInPlace
    
End Sub

Sub clearColumnFilters(WS As Worksheet)

    Call FilterColumnsClear(WS.Name)
    
End Sub
Sub testFilterColumnB()

    Call FilterColumn("TestGraph", "B:B")
    
End Sub

Open in new window


You can see the routine called FilterColumn(sWS,Cols) routine, with sWS being the name of the worksheet where the data is, and Cols reflecting the COLUMN that needs to be filtered.
 
So,  calling FilterColumn("TestGraph","H:H") would filter the H column.

Note there's a clearColumnsFilter() routine that also gets called when you activate the sheet "TestGraph" - you'll find the calling routine in that sheet's codepage (remember, right click on the TestGraph sheet tab and click VIEW CODE).

Make sense?

Don't hesitate to ask questions, if any!

Dave
TestGraph-r3.xlsm
0
 
LVL 10

Author Comment

by:LennyGray
ID: 35243984
Hi Dave -

Just a note to say "thanks".

Your solution was graceful and easy.

Lenny
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question