Numbering a sequential list, but exclude a list of given numbers.

[Fiebuls]

Hello Excel-experts,

PROBLEM:
The user defines a MINIMUM and a MAXIMUM value (whole numbers) as well as a list of some numbers to be excluded.
Required is:
1. A list with all numbers from MIN to MAX without the numbers from the exclude-list.
2. All numbers from the required list should be numbered sequentially in a separate row.


Please check out my sample worksheet in which you can see my (poor) attempt at solving my numbering-problem as well as three examples of what I would like to accomplish. I also give some comments of what means what and what doesn't work.


Any help is really appreciated.


Kind regards,
Fie Buls

P.S. I hope the attaching of the worksheet will work... if not I will look in the help to figure out how to do it :-)

[daddylonglegs]

Although you aren't working with dates you could use the WORKDAY function to solve this, with MIN in B2, MAX in B3 and numbers to exclude in B4:F4 try this formula in B7 copied across

=IF(WORKDAY($B2-1,COLUMNS($B7:B7),$B4:$F4)>$B3,"",WORKDAY($B2-1,COLUMNS($B7:B7),$B4:$F4))

that works independently of any values in your "no." row but if you want a formula for that you can base it on row 7, i.e. in B6 copied across

=IF(B7="","",COUNT($B7:B7))

edit: I subsequently realised this won't work, see revised version in my next post......

[FDibbins]

how the heck do you dream these things up, DDL??? i would love to see inside your head!! (hmm or maybe i wouldnt? lol)

[daddylonglegs]

how the heck do you dream these things up, DDL???

Thanks FD, but I think that last one was more of a nightmare than a dream because it doesn't work

You would actually need WORKDAY.INTL function (only available in Excel 2010). For that to work try this formula in B6 copied across

=IF(COLUMNS($B6:B6)>$B3-$B2-COUNT($B4:$F4)+1,"",N(A6)+1)

and then in B7 this formula copied across too

=IF(B6="","",WORKDAY.INTL($B2-1,B6,"0000000",$B4:$F4))

It relies upon the exclude range in B4:F4 not containing duplicates

[WHER]

Another possibility.

[Fiebuls]

Hello DDL,

I must agree with F Dibbens. The idea of using workdays for this problem is truly amazing. I work with excel 2011 on a Mac so the Workday.INTL function is available for me, although I have not yet the slightest idea how or why this works . Still it works, so I am truly a happy man!

If you do not like to spend time on remark III below it is not a problem. I really appreciate your efforts to come up with a good solution to my problem already, but working with your formulas I stumbled upon a situation which I tried to explain in remark III below.


A big thanks once again!


=======
Remarks
=======

(I)
I found out (without knowing why by the way) that the exclude list does not even need to be ordered sequentially. So this is a an extra bonus for me when I later realise I need to exclude some more numbers. YES!!!


(II)
I googled the function "workday.intl" and read that this function excludes weekends and holidays. Not having a clue (yet) how this function works I wonder if the usage of the function "workday.intl" in my problem, so without really using dates, will ever NOT WORK properly because of the exclusion of holidays? Does this make sense what I am asking?


(III)
The only thing the formulas do not account for is the situation where a number in the exclude list is GREATER than the MAX-value in B3. Of course I should be cautious to exclude only the correct numbers. I could also work with conditional formatting to solve this situation so I will notice when I exclude a number which is too great (greater than MAX).

However!!! Is there a way to "omit" numbers in the "exclude" list that are greater than MAX in the formula of cell B6 and across?
In that case:
* if I exclude a number too great by accident it won't effect the lists in row 6 and 7.
* I can enter numbers greater than MAX which WILL BE EXCLUDED when I increase the max-value later.




Kind regards,
Fie Buls