display unique numbers only.

[notmyday]

I have lottery numbers in cell a1:a10 in cell b1:b7 i have lottery numbers i need to find the 3 unique numbers that are missing in b1:b7
a
01 06 22 25 30
01 06 22 25 31
01 06 22 25 32
01 06 22 25 33
01 06 22 25 34
01 06 22 25 35
01 06 22 25 36
01 06 22 26 27
01 06 22 26 28
01 06 22 26 29
b
01 06 22 25 30
01 06 22 25 31
01 06 22 25 33
01 06 22 25 34
01 06 22 25 35
01 06 22 25 36
01 06 22 26 29

[alansidman]

v	A	B	C	D
1	01 06 22 25 30*	01 06 22 25 30*		=VLOOKUP(A1,$B$1:$B$7,1,0)
2	01 06 22 25 31*	01 06 22 25 31*		=VLOOKUP(A2,$B$1:$B$7,1,0)
3	01 06 22 25 32*	01 06 22 25 33*		=VLOOKUP(A3,$B$1:$B$7,1,0)
4	01 06 22 25 33*	01 06 22 25 34*		=VLOOKUP(A4,$B$1:$B$7,1,0)
5	01 06 22 25 34*	01 06 22 25 35*		=VLOOKUP(A5,$B$1:$B$7,1,0)
6	01 06 22 25 35*	01 06 22 25 36*		=VLOOKUP(A6,$B$1:$B$7,1,0)
7	01 06 22 25 36*	01 06 22 26 29		=VLOOKUP(A7,$B$1:$B$7,1,0)
8	01 06 22 26 27*			=VLOOKUP(A8,$B$1:$B$7,1,0)
9	01 06 22 26 28*			=VLOOKUP(A9,$B$1:$B$7,1,0)
10	01 06 22 26 29			=VLOOKUP(A10,$B$1:$B$7,1,0)

Those cells returning N/A are the unique numbers in A that are not in B.

[notmyday]

Thank you! Now with that part done i will work on the next part. It is returning #na so in f1 how can i make it return the cell a1?

[alansidman]

In F1, =IF(ISERROR(E1),A1,"") and copy down

v	A	B	C	D	E	F
1	01 06 22 25 30*	01 06 22 25 30*			01 06 22 25 30*
2	01 06 22 25 31*	01 06 22 25 31*			01 06 22 25 31*
3	01 06 22 25 32*	01 06 22 25 33*			#N/A	01 06 22 25 32*
4	01 06 22 25 33*	01 06 22 25 34*			01 06 22 25 33*
5	01 06 22 25 34*	01 06 22 25 35*			01 06 22 25 34*
6	01 06 22 25 35*	01 06 22 25 36*			01 06 22 25 35*
7	01 06 22 25 36*	01 06 22 26 29			01 06 22 25 36*
8	01 06 22 26 27*				#N/A	01 06 22 26 27*
9	01 06 22 26 28*				#N/A	01 06 22 26 28*
10	01 06 22 26 29				01 06 22 26 29

[Phuocam]

Or try this ...

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$10)/(COUNTIF($B$1:$B$7,$A$1:$A$10)=0),ROWS($C$1:C1))),"")

[notmyday]

Thanks guys these work great!! Im smiling happy now my headache can go away. How do i mark this resolved please?

[notmyday]

Thanks these work great my headache can go away now thanks so much. Now how do i mark this thread solved?