Why not just add the other numbers you want excluding to the range E3:E??
Matty
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.
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.
Why not just add the other numbers you want excluding to the range E3:E??
Matty
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.
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.
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.
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.
Yes, it is clear. Thanks for that.
Define Numbers11To19 as referrring to:
and Numbers30To39 asCode:=ROW(INDIRECT("11:19"))
B4, control+shift+enter and copy downn:Code:=ROW(INDIRECT("30:39"))
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.
Hi Aladin,
OUTSTANDING, thank you.
I tried incorporating it in code.
This works while still using the Defined Names:-
This Array Formula works Without 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
But this doesn't work Without using the Defined Names in the code:-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))),"")
It is not a problem, I was just curious why.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
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.
You are welcome.
I hope someone who knows VBA would want to take this up...I tried incorporating it in code.
This works while still using the Defined Names:-
This Array Formula works Without 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
But this doesn't work Without using the Defined Names in the code:-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))),"")
It is not a problem, I was just curious why.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
I can just use the first code above.
Thanks in advance.
Assuming too much and qualifying too much are two faces of the same problem.
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.
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:
Like this thread? Share it with others