Locate data records from scatter plot

Posted on 2014-03-31
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.
Question by:dougf1r
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
  • 5
  • 5
LVL 29

Expert Comment

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.

Author Comment

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!
LVL 29

Expert Comment

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

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?
LVL 29

Accepted Solution

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.

Author Comment

ID: 39976401
Works perfectly. Thanks again!

Author Comment

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?
LVL 29

Expert Comment

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.


Author Comment

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

Expert Comment

ID: 39977371
your welcome.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

749 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