Exclude Numbers From A List

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
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.

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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
Why not just add the other numbers you want excluding to the range E3:E??

Matty
 

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Thanks for the reply Matty,

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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:

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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:
Rich (BB code):
=ROW(INDIRECT("11:19"))
and Numbers30To39 as
Rich (BB code):
=ROW(INDIRECT("30:39"))
B4, control+shift+enter and copy downn:
Rich (BB 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:

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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...
 

S.H.A.D.O.

Well-known Member
Joined
Sep 6, 2005
Messages
1,915
Thanks for your help and time on this Aladin and the brilliant solution.
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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:
[/COLOR]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[COLOR=#574123]

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:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,413
Messages
5,528,625
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top