List all numbers between 2 numbers that don't appear in another column

[PeteBrown]

Hi,

I have a number in A2 [0400000000] and another number in A3 [0401000000].
I have a list of numbers in another column - B - each of which are also between these two numbers in A1 & A2.

How do I print out in Column C all the numbers available between A2 & A3 that have not been used already in Column B?

Furthermore - as we use available numbers from Column C they will be added to the end of the list in Column B - so the available numbers in Column C should dynamically adjust when a number is added to Column B.

thx in advance !!

 

[StephenCrump]

Welcome to the Forum!

How do your numbers increment? If you allow 0400000001, 0400000002, 0400000003 .... , then you'll have a million numbers in columns B and C?

 

[PeteBrown]

Hi - thanks for the reply!!

How do your numbers increment? - yes they will increment by 1.

I'd be happy to have only the first 500 or 1,000 available numbers be provided at a time - is there a way to include a fixed number of numbers to be presented as part of the solution?

 

[PeteBrown]

Sorry for the typo above .. I meant to say:
========================================
Hi - thanks for the reply!!

How do your numbers increment? - yes they will increment by 1.

I'd be happy to have only the first 500 or 1,000 available numbers be provided at a time - is there a way to include a fixed number to be listed as part of the solution?

 

[StephenCrump]

Like this?

	A	B	C	D	E	F
1	Range	Used		Available	N	15
2	0400000000	0400000003		0400000000
3	0401000000	0400000004		0400000001
4		0400000006		0400000002
5		0400000007		0400000005
6		0400000008		0400000009
7		0400000010		0400000011
8		0400000013		0400000012
9		0400000017		0400000014
10		0400000018		0400000015
11		0400000020		0400000016
12		0400000023		0400000019
13		0400000027		0400000021
14		0400000028		0400000022
15				0400000024
16				0400000025
17
Sheet1

Cell Formulas
Range	Formula
D2:D16	D2	=LET(s,SEQUENCE(A3-A2+1,,A2),INDEX(FILTER(s,ISERROR(MATCH(s,Used,))),SEQUENCE(N)))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
N	=Sheet1!$F$1	D2
Used	=Sheet1!$B$2:$B$14	D2

I have assumed your numbers 0400000000 etc are actually numeric, not text.

 

[PeteBrown]

Perfect !!

Thank you

 

[StephenCrump]

Great. I'm glad we could help.