Solved

Locate data records from scatter plot

Posted on 2014-03-31
10
163 Views
Last Modified: 2014-04-03
I have an Excel scatter plot on which I have visually identified specific points for which I would like to locate within the set of records that was used to build the plot.

I can do this by:
1) hovering over the point to determine the exact x,y values for each point of interest
2) manually scanning the data table to find the x,y pair that matches these values

However, this cumbersome if there are more than a few points of interest or multiple plots to investigate.

Suggestions for how to accomplish this task in a more automated/interactive fashion are appreciated. Thank you in advance.
0
Comment
Question by:dougf1r
  • 5
  • 5
10 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39969221
Well when you talk about 'automated/interactive' I say VBA. so if this is the route you prefer to go I first suggest you to post your sample workbook so that the solution would be catered to your specific case, then I would suggest I put in your sheet a textbox and a button called locate and you fill in the text box with a value and it will locate it in the graph ?

Will need your comments as to how you want that and if this is workable for you.
gowflow
0
 
LVL 1

Author Comment

by:dougf1r
ID: 39972806
Thanks for the reply gowflow.

Here is an example workbook. The chart I would like to identify points from is indicated with a large red arrow pointing down. I also included a button called locate.

The idea to enter a value in a text box and use the locate button is a good one. However, do you think there is be a way to click on a point in the chart and have the values associated with the point become highlighted in the table? This way, if there were several points to identify, it would avoid having to manually enter the values in the text box each time.

Thanks for your help!
IdentifyPoints.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39974544
ok I like challenges and the ones you put are really nice as total new area for me.

Here is what I did.
The button that you created 'Locate' has been called Reset Highlight and beside it there is a yellow label that will register a count of highlights.

Each time you click on a point in the graph (Chart 4) that you pointed out (I only applied this routine to this chart only) then the selected point will be highlighted in yellow in the table and the counter will increment the highlighted items.

The idea is to be able to rest highlighting which when you press on the button it will remove all yellow highlighting from the table and reset  the counter.

Try it and let me know.

As far as code this is the Main routine but for sure there are more smaller routines that you can find in the attached workbook.

Public WithEvents Chrts As Chart


Private Sub Chrts_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim WS As Worksheet
Dim Ser As Series
Dim Pt As Point
Dim PtRng As Range
Dim RngPoint As String
Dim Frmla As Variant

'---> If all selected then Exit
If Arg2 = -1 Then Exit Sub

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

Set WS = ActiveSheet

If InStr(1, Chrts.Name, "Chart 4") <> 0 Then
    'Chrts.GetChartElement x, y, IDNum, a, b
    If ElementID = xlSeries Then
        Set Ser = Chrts.SeriesCollection(Arg1)
        Set Pt = Chrts.SeriesCollection(Arg1).Points(Arg2)
        Frmla = Split(Ser.Formula, ",")
        RngPoint = Frmla(1)
        For Each PtRng In WS.Range(RngPoint)
            WS.Range(PtRng.Offset(Arg2 - 1, 0).Address).Interior.ColorIndex = 6
            WS.Range(PtRng.Offset(Arg2 - 1, 1).Address).Interior.ColorIndex = 6
            HighlightCount.Caption = Val(HighlightCount.Caption) + 1
            Exit For
        Next PtRng
        
    End If
    
End If


'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
End Sub

Open in new window


gowlfow
IdentifyPoints-V01.xlsm
0
 
LVL 1

Author Comment

by:dougf1r
ID: 39975797
This is exactly the functionality I was looking for gowlfow!

The only thing is that I get an error (attached) when I open the file. If I close debugger, I can get the highlighter to work after I click around a few times on the points and the reset button.

Any ideas on why I get the error?
CompileError.PNG
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39976172
You are very much correct. I am sorry for this it was maybe a last minute change. I had to re-think the opening of the file so that the macro launches automatically. The problem here is that it is not easy to capture Chart events this is why we need to create a variable for that and the problem is the initialization of the variable once the file opened.

It has been (I hope fixed in this version)

Let me know.
gowflow
IdentifyPoints-V02.xlsm
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:dougf1r
ID: 39976401
Works perfectly. Thanks again!
0
 
LVL 1

Author Comment

by:dougf1r
ID: 39976408
Can you please indicate where in the code I would need to make modifications for this to apply to a different chart that may exist in the same workbook or another workbook?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39976441
Well it is not automatic ! Code has to be applied depending on the chart. But basically this is the main instruction that decide to handle chart 4
If InStr(1, Chrts.Name, "Chart 4") <> 0 Then

you may change "Chart 4" with a string of an other chart and just try (I say well TRY) to see if this only change could give you the expected result. But then again you have the button and the label that record highlighting that need duplication and you have to check this instruction in all of the following Sub

Sub SetChart()
Sub ClearHighlight()
Private Sub Chrts_Select

Again it is not guarantee that it will work correctly by changing these as then you would have more then 1 chart being monitored and do not know the behavior at this point.

gowflow
0
 
LVL 1

Author Comment

by:dougf1r
ID: 39976661
Ok. Thanks for these tips on modifying the code for other charts.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39977371
your welcome.
gowflow
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 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

12 Experts available now in Live!

Get 1:1 Help Now