# Exclude Numbers From A List

#### S.H.A.D.O.

##### Well-known Member
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!

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Matty

##### Well-known Member
Why not just add the other numbers you want excluding to the range E3:E??

Matty

#### S.H.A.D.O.

##### Well-known Member

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.

##### MrExcel MVP

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.

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

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.

##### MrExcel MVP

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.

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

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.

##### MrExcel MVP

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.

I hope someone who knows VBA would want to take this up...

#### S.H.A.D.O.

##### Well-known Member
Thanks for your help and time on this Aladin and the brilliant solution.

#### circledchicken

##### Well-known Member
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:

Replies
8
Views
65
Replies
0
Views
38
Replies
3
Views
54
Replies
7
Views
110
Replies
11
Views
212