Find Discontinued Number

"Harlan Grove" skrev i en meddelelse
...
"Leo Heuser" wrote...
"Harlan Grove" skrev i en meddelelse
MIN(ChkNumLst)+ROW(INDIRECT("1:"&MIN(MAX(ChkNumL st)-MIN(ChkNumLst),
65535)))+65535*(COLUMN(INDIRECT("RC1:RC"&ROUNDUP ((MAX(ChkNumLst)
-MIN(ChkNumLst))/65535,0),0))-1)

This can produce huge arrays that can really slow Excel down, but this
approach *can* be used to go beyond the n..n+65536 range limit.

Assuming ChkNumLst contains the numbers 1,2,3,66000 (in e.g. D14),
I take it, that your formula is supposed to return the array
{4,5,6,,,,,65999}?

No, it should return {2,65537;3,65538;4,65539;...;65536,131071}.

Thanks!


If my assumption is correct, I would expect

=INDEX(MIN(ChkNumLst)+ROW(INDIRECT("1:"&MIN(MAX(C hkNumLst)
-MIN(ChkNumLst),65535)))+65535*(COLUMN(INDIRECT("RC 1:RC"
&ROUNDUP((MAX(ChkNumLst)-MIN(ChkNumLst))/65535,0),0))-1),1)

to return 4, but it returns 2 !?

If you misunderstand how my expression works, don't be surprised that you
fail to anticipate what it returns.

I didn't misunderstand. I *asked* if I had understood it properly, expecting
that it would return the missing numbers (which was, what the OP wanted),
and you gave me a straight answer.
Why add these poisoneous remarks?


How would you put the returned array into the worksheet?
...

Um, the normal way, but it's not supposed to be directly entered in a
worksheet. It's meant to be fed to SMALL. Note: you can select
R1C1:R65535C50 and enter the array formula

=ROW(INDIRECT("1:65535"))+65535*(COLUMN(INDIRECT(" A:AX"))-1)

and it'll populate the range with sequential integers from 1 to 50*65535.
Can you not do this in Excel on your PC?

I tried to point out, that COUNTIF doesn't work on arrays.

OK, you're right. COUNTIF doesn't work with arrays. If the array in
question
(smallarray) is sorted, however,

LOOKUP(bigarray,smallarray)=bigarray

is more efficient than ISNA(MATCH(bigarray,smallarray,0)).

I share your enthusiasm about the LOOKUP function. Too bad, that the
range has to be sorted, or to put it another way: it would be nice,
if VLOOKUP and HLOOKUP could take a vector as their first argument.

Well, it's an assumption, but the OP's array did appear sorted, and some
data processing experience would show that finding gaps in sequences is
much
easier when the list is sorted. So for this sort of application, assuming
the list is sorted is a hint that the list should be sorted.

As for VLOOKUP and HLOOKUP, given the following table in A1:B20

A 3
D 5
E 53
G 77
G 87
H 15
L 12
L 12
L 62
M 36
O 58
O 75
P 83
R 31
S 41
T 57
U 4
V 35
Y 64
Z 15

the formula

=VLOOKUP({"A","E","I","O","U"},A1:B20,2)

if entered into a 5 column by 1 row range returns

3 53 15 75 4

And similarly for HLOOKUP. It's not that these functions can't take array
1st arguments, the problem is that they don't return arrays when fed such
1st arguments. They return something akin to what INDEX returns when fed
array 2nd or 3rd arguments.

That's what I meant.
I was talking about single-cell not multi-cells formulae.
LOOKUP can return an array, HLOOKUP and VLOOKUP can't.

LeoH

"Leo Heuser" wrote...
...
I was talking about single-cell not multi-cells formulae.
LOOKUP can return an array, HLOOKUP and VLOOKUP can't.

You're being overly broad in your language. [V|H]LOOKUP *can* return arrays when
their *3rd* argument is an array, but not when their *1st* argument is an array.
They may be the oddest functions in all of Excel. That INDEX called with array
2nd or 3rd arguments can't return arrays to single cells is at least
understandable because it normally returns range references. That's not the case
for [V|H]LOOKUP.

--
To top-post is human, to bottom-post and snip is sublime.

"Harlan Grove" skrev i en meddelelse
...
"Leo Heuser" wrote...
..
I was talking about single-cell not multi-cells formulae.
LOOKUP can return an array, HLOOKUP and VLOOKUP can't.

You're being overly broad in your language. [V|H]LOOKUP *can* return
arrays when
their *3rd* argument is an array, but not when their *1st* argument is an
array.

Overly broad!?
Maybe you're being overly narrow (if such an expression exists in English)
Obviously it's necessary to quote from my earlier writings:

Quote:

.................................it would be nice, if VLOOKUP and
HLOOKUP could take a vector as their first argument.

End quote

Combining the uppermost paragraphs with the quote should make it obvious,
that what I'm saying is, that in a single-cell formula HLOOKUP and VLOOKUP
can't return arrays, if the first argument is a vector.

Right?

LeoH

"Leo Heuser" wrote...
...
that what I'm saying is, that in a single-cell formula HLOOKUP and VLOOKUP
can't return arrays, if the first argument is a vector.

Right?

Wrong!

=VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d" ,4,40},{2;3})

returns {1,10}. You are failing to be precise.

[V|H]LOOKUP can return an array if the 3rd argument is an array NO MATTER WHAT
THE 1ST ARGUMENT MAY BE. On the other hand, [V|H]LOOKUP won't return arrays when
the 3rd argument is a scalar, but they will return bizarre, undocumented,
implicitly indexed collection objects if the 1st argument is an array.

--
To top-post is human, to bottom-post and snip is sublime.

"Harlan Grove" skrev i en meddelelse
news
"Leo Heuser" wrote...
..
that what I'm saying is, that in a single-cell formula HLOOKUP and
VLOOKUP
can't return arrays, if the first argument is a vector.

Right?

Wrong!

=VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d" ,4,40},{2;3})

returns {1,10}. You are failing to be precise.

And I feel, that you are twisting my words by giving an example, that
you wouldn't meet in the real world.

You can do:
=VLOOKUP({"a","b","c","d","e"},{"a",1,10;"b",2,20; "c",3,30;"d",4,40},{2;3})
and still get {1,10}, because the function only uses the first element in
the array,
so the function is the same as
=VLOOKUP("a",{"a",1,10;"b",2,20;"c",3,30;"d",4,40} ,{2;3}), and nobody
in their right mind would insert a vector, when it can't be used by the
function.

LeoH

"Leo Heuser" wrote...
...
You can do:
=VLOOKUP({"a","b","c","d","e"},{"a",1,10;"b",2,20 ;"c",3,30;"d",4,40},{2;3})
and still get {1,10}, because the function only uses the first element in
the array,
so the function is the same as
=VLOOKUP("a",{"a",1,10;"b",2,20;"c",3,30;"d",4,40 },{2;3}), and nobody
in their right mind would insert a vector, when it can't be used by the
function.

The following formula entered in a single cell returns 11.

=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;" c",3,30;"d",4,40},{2;3}))

Select E1:F2 and array-enter the formula

=VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d" ,4,40},{2;3})

and you get

1 3
1 3

For that matter, select E1:E2 or E1:F1 and array-enter

=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;" c",3,30;"d",4,40},{2;3}))

and you get 4!

Ain't VLOOKUP fun?!

--
To top-post is human, to bottom-post and snip is sublime.

"Harlan Grove" skrev i en meddelelse
...
"Leo Heuser" wrote...
..
You can do:

=VLOOKUP({"a","b","c","d","e"},{"a",1,10;"b",2,20 ;"c",3,30;"d",4,40},{2;3})
and still get {1,10}, because the function only uses the first element in
the array,
so the function is the same as
=VLOOKUP("a",{"a",1,10;"b",2,20;"c",3,30;"d",4,40 },{2;3}), and nobody
in their right mind would insert a vector, when it can't be used by the
function.

The following formula entered in a single cell returns 11.


=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;" c",3,30;"d",4,40},{2;3}))

So do

=SUMPRODUCT(VLOOKUP({"a","b","c","d"},{"a",1,10;"b ",2,20;"c",3,30;"d",4,40},
{2;3}))

and

=SUMPRODUCT(VLOOKUP("a",{"a",1,10;"b",2,20;"c",3,3 0;"d",4,40},{2;3}))

What's your point?



Select E1:F2 and array-enter the formula

=VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d" ,4,40},{2;3})

and you get

1 3
1 3

For that matter, select E1:E2 or E1:F1 and array-enter


=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;" c",3,30;"d",4,40},{2;3}))

and you get 4!

Ain't VLOOKUP fun?!


I seem to catch a glimpse of a pattern he

For the [V][H]LOOKUP functions:
The first and the third argument can both be vectors, but not at the
same time, i.e. if they are at the same time, one of them may be
replaced by a scalar equal to the first element of the vector. The
following rules seem to apply (array-enter where appropiate):

For single-cell entries:
First argument is a scalar, third argument may be a vector or a scalar.

For multi-cell entries:
First argument may be a vector or a scalar, third argument is a scalar.

It may not be precise enough for you, but I believe you get my drift.

I'm not stating, that I have the truth, the whole truth and nothing but the
truth, but as a starter, I believe it's OK.


LeoH

Don't you just love a good p _ _ _ _ y fight? G

--
Don Guillett
SalesAid Software

"Leo Heuser" wrote in message
...
"Harlan Grove" skrev i en meddelelse
...
"Leo Heuser" wrote...
..
You can do:


=VLOOKUP({"a","b","c","d","e"},{"a",1,10;"b",2,20 ;"c",3,30;"d",4,40},{2;3})
and still get {1,10}, because the function only uses the first element
in
the array,
so the function is the same as
=VLOOKUP("a",{"a",1,10;"b",2,20;"c",3,30;"d",4,40 },{2;3}), and nobody
in their right mind would insert a vector, when it can't be used by the
function.

The following formula entered in a single cell returns 11.



=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;" c",3,30;"d",4,40},{2;3}))

So do


=SUMPRODUCT(VLOOKUP({"a","b","c","d"},{"a",1,10;"b ",2,20;"c",3,30;"d",4,40},
{2;3}))

and

=SUMPRODUCT(VLOOKUP("a",{"a",1,10;"b",2,20;"c",3,3 0;"d",4,40},{2;3}))

What's your point?



Select E1:F2 and array-enter the formula

=VLOOKUP({"a","c"},{"a",1,10;"b",2,20;"c",3,30;"d" ,4,40},{2;3})

and you get

1 3
1 3

For that matter, select E1:E2 or E1:F1 and array-enter



=SUMPRODUCT(VLOOKUP({"a","c"},{"a",1,10;"b",2,20;" c",3,30;"d",4,40},{2;3}))

and you get 4!

Ain't VLOOKUP fun?!


I seem to catch a glimpse of a pattern he

For the [V][H]LOOKUP functions:
The first and the third argument can both be vectors, but not at the
same time, i.e. if they are at the same time, one of them may be
replaced by a scalar equal to the first element of the vector. The
following rules seem to apply (array-enter where appropiate):

For single-cell entries:
First argument is a scalar, third argument may be a vector or a scalar.

For multi-cell entries:
First argument may be a vector or a scalar, third argument is a scalar.

It may not be precise enough for you, but I believe you get my drift.

I'm not stating, that I have the truth, the whole truth and nothing but
the
truth, but as a starter, I believe it's OK.


LeoH

But...

Let's be fair: it's as important for the intellectual bucks to lock
horns as it is for the over-lauded sports people.

Alf


---
Message posted from http://www.ExcelForum.com/