Search code examples
excelexcel-formulaexcel-2010sequenceskip

Skipping certain numbers in a sequence

In Excel 2010 I have a list of sequence numbers that have been used previously which I want to reserve, and I need to create a formula that will start with a number I provide as the starting one, but bypass the ones that are on my reserved list. Both the sequence number and the reserve list numbers can be several thousand entries each. My reserved sequence numbers are:

Reserved Numbers

If I give my sequence a starting number of 150, I would want my sequence numbers to be assigned as follows:

Sequence numbers that skip reserved ones

where the sequence skips numbers 156 and 231.

I have seen a way to create a custom list, but this is just the opposite.

How might I achieve this?

Solution

  • In cell M2 enter this formula:

    =MAX(start,M1)+1+(IFERROR(MATCH(MAX(start,M1)+1,rsv,),0)>0)

    and copy downwards.

    start is the cell with the starting number (K2 in your example).

    rsv is the list of reserved numbers.

    .

    UPDATE

    Here is another formula that overcomes the problem with sequential reserved numbers:

    =INDEX(ROW(OFFSET($A$1,start-1,,999)),MATCH(1,ISERROR(MATCH(ROW(OFFSET($A$1,start-1,,999)),rsv,))*(ROW(OFFSET($A$1,start-1,,999))>MAX(M1,start)),))

    This is an array formula and must be confirmed with Ctrl+Shift+Enter.