Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Exclude Numbers From A List

  1. #1
    Board Regular S.H.A.D.O.'s Avatar
    Join Date
    Sep 2005
    Location
    London (UK)
    Posts
    1,891
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Exclude Numbers From A List

    Good afternoon,

    In cell E2 I enter a maximum whole number.
    Now in cells E3:E7 there will be another whole number(s), but possibly not in all three. These numbers will not be higher than the maximum whole number.
    What I would like is a formula in cell B4 and continuing down to list the numbers from 1 to the maximum number in cell E2 but without the numbers in cells E3:E7 being included please. The first number could be number 1 so the list would start with number 2. The last number could be the maximum number so the list would end with the maximum number LESS 1.

    So for example, if the maximum number in cell E2 is 20, and the numbers in cells E3:E7 are 4, 8, 10, 11 & 14, the list will produce the numbers:-

    01
    02
    03
    05
    06
    07
    09
    12
    13
    15
    16
    17
    18
    19
    20

    T.Valko has supplied this solution to my first question which works great.

    Quote Originally Posted by T. Valko View Post
    Create this named formula:

    Name: Numbers
    Refers to: =ROW(INDIRECT("1:"&$E$2))

    Then, enter this array formula** in B4:

    =IFERROR(1/(1/SMALL(IF(ISNA(MATCH(Numbers,E$3:E$7,0)),Numbers),ROWS(B$4:B4))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.
    But, I would like to be able to adapt it so that it also disregards say numbers from 11 to 19 and 30 to 39 in the formulas in cells B4:B? and continuing down.
    It might be other numbers but I used the above as an example.
    I hope this makes sense!

    Thanks in advance.
    Kind regards,
    S.H.A.D.O.
    -∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
    12:45, restate my assumptions.
    (1) Mathematics is the language of nature.
    (2) Everything around us can be represented and understood through numbers.
    (3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

  2. #2
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,694
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude Numbers From A List

    Why not just add the other numbers you want excluding to the range E3:E??

    Matty

  3. #3
    Board Regular S.H.A.D.O.'s Avatar
    Join Date
    Sep 2005
    Location
    London (UK)
    Posts
    1,891
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude Numbers From A List

    Thanks for the reply Matty,

    Quote Originally Posted by Matty View Post
    Why not just add the other numbers you want excluding to the range E3:E??
    Yes, I could do that, but the thing is that one or more of the numbers in the MAIN cells in E3:E7 could include one of those numbers that I want to keep, but are already excluded from the list in column B, this is what is driving me mad.
    Thanks in advance.
    Kind regards,
    S.H.A.D.O.
    -∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
    12:45, restate my assumptions.
    (1) Mathematics is the language of nature.
    (2) Everything around us can be represented and understood through numbers.
    (3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,056
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Exclude Numbers From A List

    Quote Originally Posted by S.H.A.D.O. View Post
    Thanks for the reply Matty,


    Yes, I could do that, but the thing is that one or more of the numbers in the MAIN cells in E3:E7 could include one of those numbers that I want to keep, but are already excluded from the list in column B, this is what is driving me mad.
    Thanks in advance.
    Care to elaborate on this by way of an example for, as Matty implicates, the new sets and E3:E7 should together (their union) would constitute the new Numbers?
    Last edited by Aladin Akyurek; Jan 5th, 2013 at 01:56 PM.
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular S.H.A.D.O.'s Avatar
    Join Date
    Sep 2005
    Location
    London (UK)
    Posts
    1,891
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude Numbers From A List

    Hi Aladin,

    Cell E2 will be the maximum number.
    Cells E3:E7 can be any number from 1 to the maximum number.
    Column B will be every number from 1 to the maximum number excluding those numbers in cells E3:E7.
    The numbers in cells E3:E7 are used elsewhere and work perfectly.

    Now I want to further exclude the numbers from 11 to 19 and from 30 to 39 (for example) from the formulas in column B.

    EXAMPLE ONE

    So for example, if the maximum figure in cell E2 is 30.
    The numbers in cells E3:E7 are 01, 10, 17, 18, & 30.
    The numbers produce by the formula in column B would be as follows:-

    02, 03, 04, 05, 06, 07, 08, 09, 20, 21, 22, 23, 24, 25, 26, 27, 28, & 29

    The number 01 as per the original formula.
    The number 10 as per the original formula.
    The number 17 as per the original formula PLUS because it is within the excluded numbers from 11 to 19.
    The number 18 as per the original formula PLUS because it is within the excluded numbers from 11 to 19.
    The number 30 as per the original formula PLUS because it is within the excluded numbers from 30 to 39.

    EXAMPLE TWO

    So for example, if the maximum figure in cell E2 is 30.
    The numbers in cells E3:E7 are 01, 03, 10, 20, & 29.
    The numbers produce by the formula in column B would be as follows:-

    02, 04, 05, 06, 07, 08, 09, 21, 22, 23, 24, 25, 26, 27, & 28

    The number 01 as per the original formula.
    The number 03 as per the original formula.
    The number 10 as per the original formula.
    The number 20 as per the original formula.
    The number 29 as per the original formula.

    Basically, the original formula does exactly as I want it to do, I just want to be able to adapt it to exclude additional numbers, for example, from 11 to 19 and from 30 to 39.

    I hope I have made this clear enough.
    Thanks in advance.
    Kind regards,
    S.H.A.D.O.
    -∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
    12:45, restate my assumptions.
    (1) Mathematics is the language of nature.
    (2) Everything around us can be represented and understood through numbers.
    (3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,056
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Exclude Numbers From A List

    Quote Originally Posted by S.H.A.D.O. View Post
    Hi Aladin,

    Cell E2 will be the maximum number.
    Cells E3:E7 can be any number from 1 to the maximum number.
    Column B will be every number from 1 to the maximum number excluding those numbers in cells E3:E7.
    The numbers in cells E3:E7 are used elsewhere and work perfectly.

    Now I want to further exclude the numbers from 11 to 19 and from 30 to 39 (for example) from the formulas in column B.

    EXAMPLE ONE

    So for example, if the maximum figure in cell E2 is 30.
    The numbers in cells E3:E7 are 01, 10, 17, 18, & 30.
    The numbers produce by the formula in column B would be as follows:-

    02, 03, 04, 05, 06, 07, 08, 09, 20, 21, 22, 23, 24, 25, 26, 27, 28, & 29

    The number 01 as per the original formula.
    The number 10 as per the original formula.
    The number 17 as per the original formula PLUS because it is within the excluded numbers from 11 to 19.
    The number 18 as per the original formula PLUS because it is within the excluded numbers from 11 to 19.
    The number 30 as per the original formula PLUS because it is within the excluded numbers from 30 to 39.

    EXAMPLE TWO

    So for example, if the maximum figure in cell E2 is 30.
    The numbers in cells E3:E7 are 01, 03, 10, 20, & 29.
    The numbers produce by the formula in column B would be as follows:-

    02, 04, 05, 06, 07, 08, 09, 21, 22, 23, 24, 25, 26, 27, & 28

    The number 01 as per the original formula.
    The number 03 as per the original formula.
    The number 10 as per the original formula.
    The number 20 as per the original formula.
    The number 29 as per the original formula.

    Basically, the original formula does exactly as I want it to do, I just want to be able to adapt it to exclude additional numbers, for example, from 11 to 19 and from 30 to 39.

    I hope I have made this clear enough.
    Thanks in advance.
    Yes, it is clear. Thanks for that.

    Define Numbers11To19 as referrring to:
    Code:
    =ROW(INDIRECT("11:19"))
    and Numbers30To39 as
    Code:
    =ROW(INDIRECT("30:39"))
    B4, control+shift+enter and copy downn:
    Code:
    =IFERROR(1/(1/SMALL(IF(ISNA(MATCH(Numbers,E$3:E$7,0)),
      IF(ISNA(MATCH(Numbers,Numbers11To19,0)),
       IF(ISNA(MATCH(Numbers,Numbers30To39,0)),Numbers))),
        ROWS(B$4:B4))),"")
    Last edited by Aladin Akyurek; Jan 5th, 2013 at 05:21 PM.
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    Board Regular S.H.A.D.O.'s Avatar
    Join Date
    Sep 2005
    Location
    London (UK)
    Posts
    1,891
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude Numbers From A List

    Hi Aladin,

    OUTSTANDING, thank you.

    I tried incorporating it in code.
    This works while still using the Defined Names:-

    Code:
    Sub With_Named()
        
        Range("B4").FormulaArray = "=IFERROR(1/(1/SMALL(IF(ISNA(MATCH(Numbers,E$3:E$7,0))," & _
        "IF(ISNA(MATCH(Numbers,Numbers11To19,0))," & _
        "IF(ISNA(MATCH(Numbers,Numbers30To39,0)),Numbers))),ROWS(B$4:B4))),"""")"
        
        Range("B4:B52").FillDown
        
    End Sub
    This Array Formula works Without using the Defined Names:-

    Code:
    =IFERROR(1/(1/SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:"&$E$2)),E$3:E$7,0)),IF(ISNA(MATCH(ROW(INDIRECT("1:"&$E$2)),ROW(INDIRECT("11:19")),0)),IF(ISNA(MATCH(ROW(INDIRECT("1:"&$E$2)),ROW(INDIRECT("30:39")),0)),ROW(INDIRECT("1:"&$E$2))))),ROWS(B$4:B4))),"")
    But this doesn't work Without using the Defined Names in the code:-

    Code:
    Sub WithOut_Named()
        
        Range("B4").FormulaArray = "=IFERROR(1/(1/SMALL(" & _
        "IF(ISNA(MATCH(ROW(INDIRECT(1:&$E$2)),E$3:E$7,0))," & _
        "IF(ISNA(MATCH(ROW(INDIRECT(1:&$E$2)),ROW(INDIRECT(11:19)),0))," & _
        "IF(ISNA(MATCH(ROW(INDIRECT(1:&$E$2)),ROW(INDIRECT(30:39)),0)),ROW(INDIRECT(1:&$E$2))))),ROWS(B$4:B4))),"""")"
        
        Range("B4:B52").FillDown
        
    End Sub
    It is not a problem, I was just curious why.
    I can just use the first code above.

    Thanks in advance.
    Kind regards,
    S.H.A.D.O.
    -∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
    12:45, restate my assumptions.
    (1) Mathematics is the language of nature.
    (2) Everything around us can be represented and understood through numbers.
    (3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,056
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Exclude Numbers From A List

    Quote Originally Posted by S.H.A.D.O. View Post
    Hi Aladin,

    OUTSTANDING, thank you.
    You are welcome.

    I tried incorporating it in code.
    This works while still using the Defined Names:-

    Code:
    Sub With_Named()
        
        Range("B4").FormulaArray = "=IFERROR(1/(1/SMALL(IF(ISNA(MATCH(Numbers,E$3:E$7,0))," & _
        "IF(ISNA(MATCH(Numbers,Numbers11To19,0))," & _
        "IF(ISNA(MATCH(Numbers,Numbers30To39,0)),Numbers))),ROWS(B$4:B4))),"""")"
        
        Range("B4:B52").FillDown
        
    End Sub
    This Array Formula works Without using the Defined Names:-

    Code:
    =IFERROR(1/(1/SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:"&$E$2)),E$3:E$7,0)),IF(ISNA(MATCH(ROW(INDIRECT("1:"&$E$2)),ROW(INDIRECT("11:19")),0)),IF(ISNA(MATCH(ROW(INDIRECT("1:"&$E$2)),ROW(INDIRECT("30:39")),0)),ROW(INDIRECT("1:"&$E$2))))),ROWS(B$4:B4))),"")
    But this doesn't work Without using the Defined Names in the code:-

    Code:
    Sub WithOut_Named()
        
        Range("B4").FormulaArray = "=IFERROR(1/(1/SMALL(" & _
        "IF(ISNA(MATCH(ROW(INDIRECT(1:&$E$2)),E$3:E$7,0))," & _
        "IF(ISNA(MATCH(ROW(INDIRECT(1:&$E$2)),ROW(INDIRECT(11:19)),0))," & _
        "IF(ISNA(MATCH(ROW(INDIRECT(1:&$E$2)),ROW(INDIRECT(30:39)),0)),ROW(INDIRECT(1:&$E$2))))),ROWS(B$4:B4))),"""")"
        
        Range("B4:B52").FillDown
        
    End Sub
    It is not a problem, I was just curious why.
    I can just use the first code above.

    Thanks in advance.
    I hope someone who knows VBA would want to take this up...
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    Board Regular S.H.A.D.O.'s Avatar
    Join Date
    Sep 2005
    Location
    London (UK)
    Posts
    1,891
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude Numbers From A List

    Thanks for your help and time on this Aladin and the brilliant solution.
    Kind regards,
    S.H.A.D.O.
    -∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
    12:45, restate my assumptions.
    (1) Mathematics is the language of nature.
    (2) Everything around us can be represented and understood through numbers.
    (3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

  10. #10
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude Numbers From A List

    Quote Originally Posted by Aladin Akyurek View Post
    You are welcome.

    I hope someone who knows VBA would want to take this up...
    Hi S.H.A.D.O.,

    I think you are missing the quotes in the INDIRECT formula. As the quotes need to be within the existing string, you need
    two of them (""). In addition you may run into problems with your method if the array formula exceeds 255 characters.

    Try something like:

    Code:
    Sub WithOut_Named()    
        With Range("B4")
            .Formula = _
                "=IFERROR(1/(1/SMALL(" & _
                "IF(ISNA(MATCH(ROW(INDIRECT(""1:""&$E$2)),E$3:E$7,0))," & _
                "IF(ISNA(MATCH(ROW(INDIRECT(""1:""&$E$2)),ROW(INDIRECT(""11:19"")),0))," & _
                "IF(ISNA(MATCH(ROW(INDIRECT(""1:""&$E$2)),ROW(INDIRECT(""30:39"")),0))," & _
                "ROW(INDIRECT(""1:""&$E$2))))),ROWS(B$4:B4))),"""")"
            .FormulaArray = .FormulaR1C1
            .Resize(49, 1).FillDown
        End With
    
    End Sub


    For more on using worksheet formulae in VBA (and the double quoting) see:


    For an excellent article on working with array formulae in vba see:

    For the 255 character limitation and workarounds see:

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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