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.

 

[Trebor76]

Hi S.H.A.D.O. (again),

I don't think you can do this via a formula but the following macro will do the job:

Option Explicit

Sub Macro1()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au

    Dim lngMyNum As Long
    
    If IsNumeric(Range("M2")) = False Or Len(Range("M2")) = 0 Then
        MsgBox "There is either no maximum value entered in cell M2 or it's a text string." & vbNewLine & "Enter your desired maximum number and try again."
        Exit Sub
    End If
    
    Application.ScreenUpdating = False

    For lngMyNum = 1 To Range("M2")
        If IsError(Evaluate("VLOOKUP(" & lngMyNum & ",M3:M5,1,FALSE)")) = True Then
            Range("N" & Cells(Rows.Count, "N").End(xlUp).Row + 1).Value = lngMyNum  'Output to Col. N. Change to suit.
        End If
    Next lngMyNum
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[easy2understandexcel]

I don't have excel on this laptop to test this, but if you want to try it by formula: On sheet2 column A make a list of number from 1~1000 (or whatever the highest potential max would be). In column B put a formula that does vlookup for the exclusion numbers and the maximum number. In column C if it is an exclusion number or past the maximum number the column C result is "0"; the rest of the column C results should be "1" (by the formula result). Column D multiply column A by column C. Now back on sheet 1 add a column that has 2~1000 (lets call this column A starting on A1 for simplicity). Your desired list will go next to this in column B, put "1" in B1. In B2 put formula: =min(sheet2!D:D,sheet1!a1), drag formula down. Anyway, the concept is that the list in sheet2!D:D changes all higher and exempt numbers to zero, then starting with the 2nd highest number (excluding zero) the list will populate on sheet1!B:B. Like I said before, I don't have excel handy to test this or be more specific, but I think it should work

 

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

Hi Robert (again!),

Thanks very much, it does what it says on the tin.
I have been mucking around with a formula version for ages now, but to no avail!
I will have a play around to get it to start the input in cell B4 and continue down and also if I run it more than once that it doesn't just add the results underneath the previous ones but starts again in cell B4.

Thanks so much again for your help and time, it is apprecitaed.

 

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

Hi easy2understandexcel,

I am just on my way out but I will try your suggestion later or in the morning, thanks.

 

[Scott Huish]

Sub countup()
Dim m As Long, c As Long
c = 3
For m = 1 To Range("M2")
    If m <> Range("M3") And m <> Range("M4") And m <> Range("M5") Then
        c = c + 1
        Cells(c, 2).NumberFormat = "00"
        Cells(c, 2) = m
    End If
Next
End Sub

 

[Snakehips]

S.H.A.D.O.

EDIT - Sorry this was a mis-post!!!

 

[Trebor76]

As long as cell B3 is kept blank, you can try this formula in cell B4...

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

...and then copy it down as far as required (to cell B15 for 3 numbers to be excluded).

Regards,

Robert

Regards,

Robert

 

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

Sub countup()
Dim m As Long, c As Long
c = 3
For m = 1 To Range("M2")
    If m <> Range("M3") And m <> Range("M4") And m <> Range("M5") Then
        c = c + 1
        Cells(c, 2).NumberFormat = "00"
        Cells(c, 2) = m
    End If
Next
End Sub

Thanks HOTPEPPER,

That's short and sweet and works perfectly, thanks.

 

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

As long as cell B3 is kept blank, you can try this formula in cell B4...

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

...and then copy it down as far as required (to cell B15 for 3 numbers to be excluded).

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