Closed Thread
Results 1 to 20 of 20

Find closest match unsorted data

  1. #1
    Registered User
    Join Date
    04-07-2008
    Location
    Adelaide, SA
    Posts
    87

    Find closest match unsorted data

    Hi all,

    I am wondering if it is possible to search a column full of assorted values which is unsorted, and then return the closest value.

    I have written a previous thread which was similar, but misleading, i feel this thread is more to the point.

    Currently i use this code, but it only finds the closest highest value if the data is sorted in ascending order.

    Please Login or Register  to view this content.
    Can anyone help?

    Thanks,

    Yappa

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    You maybe able to adapt 1 of these to your needs

    http://www.exceltip.com/st/Retrievin...Match/993.html

    or

    http://kb.wisc.edu/helpdesk/page.php?id=1237

    or

    http://www.business-spreadsheets.com/forum.asp?t=12
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Find closest match unsorted data

    with
    B1: (a target number)

    This formula returns the closest number (higher/lower/or exact) to the target number:
    Please Login or Register  to view this content.
    Examples:
    If you want the closest match to 49 in the series {48, 51, 55},
    the formula returns: 48
    If you want the closest match to 50 in the series {48, 51, 55},
    the formula returns: 51


    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    04-07-2008
    Location
    Adelaide, SA
    Posts
    87
    Thankyou mudraker and Ron Coderre for your replies.

    mudraker that first link you supplied I had found also in my search for the answer to my problem, along with some other similar sites with varied ideas.

    I've tried making these excel formulas work through vba but unfortunately I don't know how to exactly.

    Ron Coderre, your formula looks like it will do exactly what I want, but again I am unsure how to make this work through vba. I think I may have also mislead you with using cl as range, it is not C1.

    Here is my attempt to get it to work, but I have had no success.

    Please Login or Register  to view this content.
    throws the error:
    Run Time error '13'
    Type Mismatch

    Hope you guys can still help.

    Thanks,

    Yappa
    Last edited by Yappa; 06-26-2008 at 12:04 PM.

  5. #5
    Registered User
    Join Date
    04-07-2008
    Location
    Adelaide, SA
    Posts
    87
    I've still been messing around with this line of code, but it simply will not work.

    I have now included an attachment with the code I am using.

    I also have the excel formula written in cell C1 which was supplied by Ron Coderre and it works perfect - as whatever number I write in cell B1, cell C1 shows the closest value.

    The vba code I am using, reads the value entered in cell B1, and should post the closest value in cell B2, but it keeps throwing the error:
    Run Time error '13'
    Type Mismatch
    When trying to locate this error in the code, it brings up the lines which use the Abs function, maybe thats the area where the problem lies - I have no idea.

    Please can someone throw me a line here, this is very frustrating.

    Thanks,

    Yappa
    Attached Files Attached Files

  6. #6
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,204
    When entered on a sheet, those MATCH formulas are entered as array formulas. WorksheetFunction.Match only accepts a single value as the first argument.

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  7. #7
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105
    This is by no means an elegant solution, as it's really just temporarily using the worksheet function as it exists, but it works for a result... Probably not what you're looking for though.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-07-2008
    Location
    Adelaide, SA
    Posts
    87
    Thankyou both mikerickson and beeawwb for your replies.

    beeawwb, there is nothing wrong with your suggestion, and it works fine, I would prefer the formula not to be pasted and cleared into the excel sheet.

    mikerickson, I am not sure exactly how your code works, but it works fine also. I have chosen to work with yours.

    Would either of you be kind enough to help me further with this?

    Once the closest value is determined from column A, it needs to then find that value in column A and take reference to that cell position (which is why I was setting rg as range) and from there I can then offset the selection cursor, to retrieve values from other positions in the worksheet.

    The offset part I am ok with, it's just establishing the cell reference that I am having difficulties with.

    I have included another attachment with the new code adjustment, which doesn't work at all, but hopefully it gives you enough idea of what i am trying to achieve. It's called FindClosest. The other sub FindClosest2, works perfect for what i was trying to achieve initially.

    Any suggestions?

    Thanks,

    Yappa
    Attached Files Attached Files

  9. #9
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,204
    What the code is doing is putting an array formula in B2 and then replacing the formula with it's calculaed value.
    This will set that cell reference.
    Please Login or Register  to view this content.
    Last edited by mikerickson; 06-27-2008 at 09:03 AM.

  10. #10
    Registered User
    Join Date
    04-07-2008
    Location
    Adelaide, SA
    Posts
    87
    Thanks for your continued support mikerickson.

    I am having trouble with the added lines of code you supplied, it keeps showing the error - Run-time error '424': object required. I tried declaring:

    Please Login or Register  to view this content.
    but that makes no difference.

    Hope you can guide me further with this, as I am unsure where to go from here.

    Thanks,

    Yappa

  11. #11
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,204
    This should fix that
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    04-07-2008
    Location
    Adelaide, SA
    Posts
    87
    Sorry to be a pain, mikerickson, but I am still getting the same error.

    Continued help appreciated.

    Thanks,

    Yappa

  13. #13
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,204
    I can't reproduce the error.
    Can you post the code you are using?

  14. #14
    Registered User
    Join Date
    04-07-2008
    Location
    Adelaide, SA
    Posts
    87
    Please Login or Register  to view this content.

  15. #15
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655
    There's a typo:
    Please Login or Register  to view this content.
    ... should be:
    Please Login or Register  to view this content.
    ... or better (for Intellisense to work):
    Please Login or Register  to view this content.

  16. #16
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,204
    Shg,
    Thanks for catching my error.

  17. #17
    Registered User
    Join Date
    04-07-2008
    Location
    Adelaide, SA
    Posts
    87
    Thanks shg for finding the problem, i feel kind of stupid for not seeing that myself.

    It works perfect now.

    Very special thanks to mikerickson, your help has been fantastic.

    Also thanks to all else who helped direct me to a solution.

    Yappa.

  18. #18
    Registered User
    Join Date
    04-07-2008
    Location
    Adelaide, SA
    Posts
    87
    I have a follow up question to this if anyone would like to help?

    Now that the closest match can be found, I thought that establishing the cell position would enable me to offset from that position to find another value in the workbook.

    I've tried doing this but have had no success.

    I have supplied a new workbook attachment with an altered code which supplies the same result called FindClosest, and also another code called FindClosest2 which I am trying to get to work to use the found closest match value, and offset from that cell and return the new designated cell value.

    I'll show codes here too, incase they do not convert properly to .xls format as I am using excel 2007:

    Please Login or Register  to view this content.
    I hope someone can help, as this was my main objective. I thought I would be able to complete the last part, but being new to this use of coding I am stuck.

    Thanks,

    Yappa
    Attached Files Attached Files
    Last edited by Yappa; 06-30-2008 at 02:21 PM.

  19. #19
    Registered User
    Join Date
    02-11-2010
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Find closest match unsorted data

    Hi,

    I have have a large array of prices (across rows) and am looking for the closest price to match a price that I have been provided with. It's a basic benchmarking exercise....and the price can be positive or negative. Is there a clean way to pull back the closest price?

    I have come across a fair amount of solutions, but none worked optimally - particularly the =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) approach....it just didn't work for some lines, and only worked for values less than source price in other instaneces.

    I would also like to reference the source on the next column.

    Perhaps you can help?

    Thanks very much,

    Peter

  20. #20
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Find closest match unsorted data

    Peter, welcome to the board, however, please note you should ask your question in your own thread rather than in that of another member.

    If you ask again in a new thread you will get a prompt response.

    Thanks

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1