I am hoping someone can help construct some kind of Lookup+Find/Match formula
to check a column of numbers using a constant array provided e.g.
{1,2,3,4,5,6,7,8} and return the numbers in the column that are missing (if
any). The will be duplicate numbers and blanks, but I want to find out if any
of the numbers in the 1-8 array do not appear at all in the column. Numbers
may vary by column but will be no greater than 8, i.e. col.B: 1-5, col.C:
1-7. For the example below the results should be: Col.A is missing numbers 4
& 7; Col.B is missing number 3; Col.C is missing number 1. Unfortunately I
have no idea how to make this happen. I would greatly appreciate any
responses to help. Thank you!
A B C
2 1 3
2 4
8 7
2 4
3 1 2
1 5 5
6 7
5 6

Try this...

Create this defined name...

Goto the menu InsertNameDefine
Name: Nums
Refers to: =ROW(INDIRECT("1:8"))
OK

Then, assume your numbers are in the range A2:A9.

Enter this array formula** in say, C2:

=SMALL(IF(ISNA(MATCH(Nums,A$2:A$9,0)),Nums),ROWS(C $2:C2))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get #NUM! errors meaning all missing numbers have be
returned.

--
Biff
Microsoft Excel MVP


"Excel-User-RR" wrote in message
...
I am hoping someone can help construct some kind of Lookup+Find/Match
formula
to check a column of numbers using a constant array provided e.g.
{1,2,3,4,5,6,7,8} and return the numbers in the column that are missing
(if
any). The will be duplicate numbers and blanks, but I want to find out if
any
of the numbers in the 1-8 array do not appear at all in the column.
Numbers
may vary by column but will be no greater than 8, i.e. col.B: 1-5, col.C:
1-7. For the example below the results should be: Col.A is missing numbers
4
& 7; Col.B is missing number 3; Col.C is missing number 1. Unfortunately I
have no idea how to make this happen. I would greatly appreciate any
responses to help. Thank you!
A B C
2 1 3
2 4
8 7
2 4
3 1 2
1 5 5
6 7
5 6

Thanks Biff, the suggestion works great. I made a slight modification to it
because some columns are 1-8, some are 1-6, some 1-5 etc. So I made a
separate defined name for each one that I needed, i.e. - Nums4, Nums5, Nums6,
Nums8. If you have a better suggestion to handle this difference, I would
definitely like to see it; otherwise I am happy will the answer as it is.
Thanks!

"T. Valko" wrote:

Try this...

Create this defined name...

Goto the menu InsertNameDefine
Name: Nums
Refers to: =ROW(INDIRECT("1:8"))
OK

Then, assume your numbers are in the range A2:A9.

Enter this array formula** in say, C2:

=SMALL(IF(ISNA(MATCH(Nums,A$2:A$9,0)),Nums),ROWS(C $2:C2))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down until you get #NUM! errors meaning all missing numbers have be
returned.

--
Biff
Microsoft Excel MVP


"Excel-User-RR" wrote in message
...
I am hoping someone can help construct some kind of Lookup+Find/Match
formula
to check a column of numbers using a constant array provided e.g.
{1,2,3,4,5,6,7,8} and return the numbers in the column that are missing
(if
any). The will be duplicate numbers and blanks, but I want to find out if
any
of the numbers in the 1-8 array do not appear at all in the column.
Numbers
may vary by column but will be no greater than 8, i.e. col.B: 1-5, col.C:
1-7. For the example below the results should be: Col.A is missing numbers
4
& 7; Col.B is missing number 3; Col.C is missing number 1. Unfortunately I
have no idea how to make this happen. I would greatly appreciate any
responses to help. Thank you!
A B C
2 1 3
2 4
8 7
2 4
3 1 2
1 5 5
6 7
5 6

What you did is about the best way to do it.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Excel-User-RR" wrote in message
...
Thanks Biff, the suggestion works great. I made a slight modification to
it
because some columns are 1-8, some are 1-6, some 1-5 etc. So I made a
separate defined name for each one that I needed, i.e. - Nums4, Nums5,
Nums6,
Nums8. If you have a better suggestion to handle this difference, I would
definitely like to see it; otherwise I am happy will the answer as it is.
Thanks!

"T. Valko" wrote:

Try this...

Create this defined name...

Goto the menu InsertNameDefine
Name: Nums
Refers to: =ROW(INDIRECT("1:8"))
OK

Then, assume your numbers are in the range A2:A9.

Enter this array formula** in say, C2:

=SMALL(IF(ISNA(MATCH(Nums,A$2:A$9,0)),Nums),ROWS(C $2:C2))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down until you get #NUM! errors meaning all missing numbers have be
returned.

--
Biff
Microsoft Excel MVP


"Excel-User-RR" wrote in message
...
I am hoping someone can help construct some kind of Lookup+Find/Match
formula
to check a column of numbers using a constant array provided e.g.
{1,2,3,4,5,6,7,8} and return the numbers in the column that are missing
(if
any). The will be duplicate numbers and blanks, but I want to find out
if
any
of the numbers in the 1-8 array do not appear at all in the column.
Numbers
may vary by column but will be no greater than 8, i.e. col.B: 1-5,
col.C:
1-7. For the example below the results should be: Col.A is missing
numbers
4
& 7; Col.B is missing number 3; Col.C is missing number 1.
Unfortunately I
have no idea how to make this happen. I would greatly appreciate any
responses to help. Thank you!
A B C
2 1 3
2 4
8 7
2 4
3 1 2
1 5 5
6 7
5 6