Solved

Excel Graph on change to data

Posted on 2011-03-03
11
284 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
  • 6
  • 5
11 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 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 41

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
 
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 41

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 41

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 41

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 41

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now