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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Why not just add the other numbers you want excluding to the range E3:E??

Matty
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top