Results 1 to 8 of 8

Thread: Finding values in a range of from/to arrays

  1. #1
    Neophyte HuRocan's Avatar
    Join Date
    Jan 2020
    Posts
    4
    Articles
    0
    Excel Version
    2013

    Finding values in a range of from/to arrays



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,

    I am new to this forum, so a bit unsure how this would work. Anyway, I have a situation where in a workbook, in one sheet, I have a list of random numbers, sorted ascending (please see example sheet attached, "data" worksheet), and in another sheet ("ranges") I have a from / to set of ranges of numbers. I need to find if a particular number in the "data" worksheet is found in any of the from/to ranges in the "ranges" worksheet. If the number is found in a range, then I'd like to know the row number of that range, input in column B of the "data" worksheet against that number. If it is not found in any of the from/to ranges, then nothing should be displayed. Any help, even if it involves VBA, would be very appreciated.

    Many thanks.
    Attached Files Attached Files
    Last edited by HuRocan; 2020-01-27 at 10:54 PM.

  2. #2
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    37
    Articles
    0
    Excel Version
    2016
    worksheet "data"

    B2=MATCH(LOOKUP(data!A2,ranges!$A$2:$A$18),ranges!A:A,)

  3. #3
    Neophyte HuRocan's Avatar
    Join Date
    Jan 2020
    Posts
    4
    Articles
    0
    Excel Version
    2013
    Thanks caabyyc, but it doesn't work properly. Example is 50041 or 50042, neither of those is found in the from/to columns in the ranges! worksheet. This is because it skips between 50010 and 50050 if you look closely. The formula proposed looks for the lower value between the looked-up value and the ranges I have defined, can't identify that the value looked up actually does not exist in the ranges proposed.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,682
    Articles
    0
    Excel Version
    365
    I note that there are gaps between some of your ranges and that none overlap, so a simple formula in B2 of the data sheet:
    Code:
    =SUMPRODUCT(($A2>=ranges!$A$2:$A$18)*($A2<=ranges!$B$2:$B$18)*ROW(ranges!$A$2:$A$18))
    will give you the row number, but will return 0 (zero) if not found. You could format the cells to hide zeroes.

    A tweak to the above will show blanks instead of zeroes:
    Code:
    =IFERROR(1/(1/SUMPRODUCT(($A2>=ranges!$A$2:$A$18)*($A2<=ranges!$B$2:$B$18)*ROW(ranges!$A$2:$A$18))),"")
    However, should some of your ranges overlap and a value can be found in two or more ranges, that needs this formula to be array-entered (using CTRL+SHIFT+ENTER to commit the formula to the sheet, rather than just ENTER) in cell B2 and then copied down:
    Code:
    =TEXTJOIN(", ",TRUE,IF(($A2>=ranges!$A$2:$A$19)*($A2<=ranges!$B$2:$B$19)>0,ROW(ranges!$A$2:$A$19),""))
    The cell will show a list of rows separated by commas.

    In all cases, the ranges on the ranges sheet do not need to be sorted.
    Last edited by p45cal; 2020-01-28 at 07:34 PM.

  5. #5
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    37
    Articles
    0
    Excel Version
    2016
    Yes, your are right, sorry for that.

    (revised) B2=IFERROR(LOOKUP(1,0/((A2>=ranges!$A$2:$A$18)*(A2<=ranges!$B$2:$B$18)),ROW(ranges!$A$2:$A$18)),"")



    Quote Originally Posted by HuRocan View Post
    Thanks caabyyc, but it doesn't work properly. Example is 50041 or 50042, neither of those is found in the from/to columns in the ranges! worksheet. This is because it skips between 50010 and 50050 if you look closely. The formula proposed looks for the lower value between the looked-up value and the ranges I have defined, can't identify that the value looked up actually does not exist in the ranges proposed.

  6. #6
    Neophyte HuRocan's Avatar
    Join Date
    Jan 2020
    Posts
    4
    Articles
    0
    Excel Version
    2013
    My ranges do not overlap, and this seems to be working, thank you very much for your help.

  7. #7
    Neophyte HuRocan's Avatar
    Join Date
    Jan 2020
    Posts
    4
    Articles
    0
    Excel Version
    2013
    Any suggestion as to how to do this with VBA? Given the reason that if there is a lot of data, like over 100,000 lines of data in the ranges! sheet, the calculations via Excel formula take forever...

    Many thanks.

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,682
    Articles
    0
    Excel Version
    365
    Specifically for the file you attached:
    Code:
    Sub blah()
    With Sheets("Data").Range("B2:B16")
    
    'either: one of these three lines:
    '  .FormulaR1C1 = "=SUMPRODUCT((RC1>=ranges!R2C1:R18C1)*(RC1<=ranges!R2C2:R18C2)*ROW(ranges!R2C1:R18C1))"
      .FormulaR1C1 = "=IFERROR(1/(1/SUMPRODUCT((RC1>=ranges!R2C1:R18C1)*(RC1<=ranges!R2C2:R18C2)*ROW(ranges!R2C1:R18C1))),"""")"
    '  .FormulaR1C1 = "=IFERROR(LOOKUP(1,0/((RC[-1]>=ranges!R2C1:R18C1)*(RC[-1]<=ranges!R2C2:R18C2)),ROW(ranges!R2C1:R18C1)),"""")"
    
    'or: these two lines for overlapping ranges:
    '  .Cells(1).FormulaArray = "=TEXTJOIN("", "",TRUE,IF((RC1>=ranges!R2C1:R19C1)*(RC1<=ranges!R2C2:R19C2)>0,ROW(ranges!R2C1:R19C1),""""))"
    '  .Cells(1).AutoFill Destination:=.Cells, Type:=xlFillDefault
    
    'convert to values:
      .Value = .Value
    End With
    End Sub
    but it writes the formula into the cells and then converts them to plain values, so it may still take ages.
    Supply a file with lots of data (link to it on a file sharing site if it's to big to be attached here) and I'll try to write vba which could be faster.

Posting Permissions

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