Solved

Locate data records from scatter plot

Posted on 2014-03-31
10
168 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

825 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