List numbers to maximum number but excluding certain numbers.

[S.H.A.D.O.]

Good afternoon, I hope everybody had a great Christmas.

In cell M2 I enter a maximum whole number.
Now in cells M3:M5 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 M2 but without the numbers in cells M3:M5 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 M2 is 15, and the numbers in cells M3:M5 are 4, 8, & 14, the list will produce the numbers:-

01
02
03
05
06
07
09
10
11
12
13
15

I hope this makes sense.
Thanks in advance.

 

[Snakehips]

S.H.A.D.O.

Sorry about previous mis-post but I realised that it failed if one of M3:M5 was empty.

The following may not be the most suitable solution but I believe it does the job provided that B3 is empty.

Excel 2007
	B	C	D	E	F	G	H	I	J	K	L	M
1
2												15
3												1
4	02											8
5	03											14
6	04
7	05
8	06
9	07
10	09
11	10
12	11
13	12
14	13
15	15
16
17
Sheet4

Cell Formulas
Range	Formula
B4	=IFERROR(IF(ROWS($B$4:B4)+SUMPRODUCT(--($M$3:$M$5<=B3+1)*--($M$3:$M$5>0))>$M$2,"",ROWS($B$4:B4)+SUMPRODUCT(--($M$3:$M$5<=B3+1)*--($M$3:$M$5>0))),"")

 

[S.H.A.D.O.]

Hi Tony,

Thanks for the reply, unfortunately this seems to fail in certain situations with the number in cell M4.
I can't seem to adjust the formula to get it to work.
Thanks in advance.

 

[T. Valko]

Good afternoon, I hope everybody had a great Christmas.

In cell M2 I enter a maximum whole number.
Now in cells M3:M5 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 M2 but without the numbers in cells M3:M5 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 M2 is 15, and the numbers in cells M3:M5 are 4, 8, & 14, the list will produce the numbers:-

01
02
03
05
06
07
09
10
11
12
13
15

I hope this makes sense.
Thanks in advance.

I see you have a VBA solution. Are you interested in a formula solution?

What is the highest max number that you will use?

What version of Excel are you using?

 

[Snakehips]

S.H.A.D.O.

Will look at it again in the morning if it is still a potential solution for you.

If it is, can you indicate sample values for M4 etc that are failing?

 

[mirabeau]

Good afternoon, I hope everybody had a great Christmas.

In cell M2 I enter a maximum whole number.
Now in cells M3:M5 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 M2 but without the numbers in cells M3:M5 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 M2 is 15, and the numbers in cells M3:M5 are 4, 8, & 14, the list will produce the numbers:-

01
02
03
05
06
07
09
10
11
12
13
15

I hope this makes sense.
Thanks in advance.

In the (no doubt unlikely) event that your problem develops a large size, say M2 is in the hundreds of thousands, with lots and lots of numbers to be excluded (listed from M3 down) then you may be interested in the following code. It should also work fine for the problem as you posted it.

Sub q()
Dim a&(), b() As Boolean, c, d&
ReDim a(1 To [m2], 1 To 1), b([m2])
For Each c In Range("M3", Range("M" & Rows.Count).End(3))
    If Len(c) > 0 Then b(c) = True
Next
For c = 1 To [m2]
    If Not b(c) Then d = d + 1: a(d, 1) = c
Next
Range("B4").Resize(d) = a
End Sub

 

[S.H.A.D.O.]

I see you have a VBA solution. Are you interested in a formula solution?

What is the highest max number that you will use?

What version of Excel are you using?

I would be interested in a formula solution please.
The maximum number would probably be no higher than 60.
I am using Excel 2007.
Thanks in advance.

 

[S.H.A.D.O.]

In the (no doubt unlikely) event that your problem develops a large size, say M2 is in the hundreds of thousands, with lots and lots of numbers to be excluded (listed from M3 down) then you may be interested in the following code. It should also work fine for the problem as you posted it.

Sub q()
Dim a&(), b() As Boolean, c, d&
ReDim a(1 To [m2], 1 To 1), b([m2])
For Each c In Range("M3", Range("M" & Rows.Count).End(3))
    If Len(c) > 0 Then b(c) = True
Next
For c = 1 To [m2]
    If Not b(c) Then d = d + 1: a(d, 1) = c
Next
Range("B4").Resize(d) = a
End Sub

Thanks mirabeau, that works brilliantly.

 

[Trebor76]

Thanks again Robert for the reply, unfortunately every number gets repeated three times.

Sorry my bad (it was very early in the morning here in Aus at the time). Try this in cell B4 (ensuring cell B3 is blank) and copied down as far as required:

=IF(ISERROR(VLOOKUP(B3+1,$M$3:$M$5,1,FALSE)),B3+1,IF(ISERROR(VLOOKUP(B3+2,$M$3:$M$5,1,FALSE)),B3+2,IF(ISERROR(VLOOKUP(B3+2,$M$3:$M$5,1,FALSE)),B3+3,B3+4)))

Note this is only for up to three missing numbers as per your original post.

Regards,

Robert

 

[mirabeau]

S.H.A.D.O.;

Thanks for the feedback.

You've got a good selection of options to choose from. That's always good in my view.

 

[S.H.A.D.O.]

Try this in cell B4 (ensuring cell B3 is blank) and copied down as far as required:

=IF(ISERROR(VLOOKUP(B3+1,$M$3:$M$5,1,FALSE)),B3+1,IF(ISERROR(VLOOKUP(B3+2,$M$3:$M$5,1,FALSE)),B3+2,IF(ISERROR(VLOOKUP(B3+2,$M$3:$M$5,1,FALSE)),B3+3,B3+4)))

Note this is only for up to three missing numbers as per your original post.

Thanks Robert, that works.
I will have a proper look at the formula in the morning and figure out how it work, thanks.
The only thing is that it shows numbers past the maximum number in cell M2 but I will have a go in the morning.
It is 1:00am here and I am off to bed to get some zzzzzzzzzzzzzzzz's.
Thanks again.