'subtract' a range of numbers from another range

[excelNewbie22]

hi!

so i have a long list of 1000 shuffled numbers with duplicates (col a) ,
which always changing,
all from within a range of 1000-1500,
(example 1001,4050, 3182, 4992, 1002, 4050 and so on)

i want to exclude constantly the first 400,
with these 400, i want to delete the duplicates,
and let's say i left with 200 unique numbers (col b),
then 'subtract' these 200's from the 500 unique numbers of the range of 1000-1500,
and then display the remaining 300 in another column (col c)

now, i've already have a way to do it,
with sorting and filtering and condition formatting,
but since i'll need to do so lot's of times,
i wonder can it be done in a shorter way?
maybe with formulas? even several?

i thought something with unique function maybe?



p.s
the range and numbers are just examples,
the originals are different,
and assuming there will be a solution i'll modify it to my needs

 

[excelNewbie22]

test.xlsx
	A	B	C
1	col a	col b -exculde first 5	col c - exclude col b num's from the entire range=1000-1500
2	1000	1000	1001
3	1011	1011	1003
4	1002	1002	1004
5	1022	1022	1005
6	1011		1006
7	1250		1007
8	1400		1008
9	1011		1009
10	1499		1010
11	1320		1012
12	and etc….		1013
13			1014
14			1015
15			1016
16			1017
17			1018
18			1019
19			1020
20			1021
21			1023
22			1024
23			and so on
test

Cell Formulas
Range	Formula
B2:B5	B2	=UNIQUE(A2:A6)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	duplicates	text	NO

 

[Fluff]

How about

=WORKDAY.INTL(999,SEQUENCE(501-ROWS(B2#)),"0000000",B2#)

As you have the Unique function, I would suggest that you update your profile, as it is not available in xl 2013

 

[excelNewbie22]

superb!
i yet to manage to apply it to my needs
cause my range of numbers start at 1111 to approx 4511 (with no 0's or 9's)
will this effect the formula?
and also my col b is with shuffled numbers, so it won't be in order (never)

 

[Fluff]

Yes it will.

 

[excelNewbie22]

so... no alternative solution?

 

[Fluff]

There is always an alternative, but I have no idea what unless you can supply accurate information on what you are trying to do.

 

[excelNewbie22]

range is 1111 to 8888 (with no 0's or 9's)
the amount of numbers for this range is 4096
col a is approx 6000 numbers incl duplicates
col b is approx 3000 unique numbers from col a
col c is the result of which the entire range mention (1111-8888) MINUS col b

 

[Fluff]

Ok, how about

=LET(w,WORKDAY.INTL(1110,SEQUENCE(7782-ROWS(B2#)),"0000000",B2#),FILTER(w,(ISERR(FIND(0,w)))*(ISERR(FIND(9,w)))))

 

[excelNewbie22]

probably my end of doing somthing wrong so i post an example
also, i can't post thousands of numbers so only sample some (col b)

test
	A	B	C
1		unique numbers
2		1317	#REF!
3		4726
4		5812
5		3147
6		1247
7		4442
8		4131
9		3464
10		4588
11		2711
12		2788
13		1822
14		4738
15		2244
16		4357
17		6322
18		6268
19		6442
20		5256
21		1746
22		8147
test

Cell Formulas
Range	Formula
C2	C2	=LET(w,WORKDAY.INTL(1110,SEQUENCE(7782-ROWS(B2#)),"0000000",B2#),FILTER(w,(ISERR(FIND(0,w)))*(ISERR(FIND(9,w)))))