On 2002-03-19 12:17, Chris Davison wrote:
drat - I need help afterall !
In the middle of setting up my first live dynamic range via :
=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1))
(it'll be all text)
I'm confused about the -1 switch : does this mean I have to always have column A sorted descendingly?
Help file says :
"If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on."
I refuse to believe it. I shouldn't have to go sorting all my info every time, should I ?
Aladin, please tell me it's a lot more complicated than that and that Dave's formula subtelly takes advantage of this with the "*" criteria....
many thanks
Chris
Chris,
It looks like you're saying that you succeeded to dynamically computed range by using
=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1))
in A of "all text" type. Don't count on it too fast. What follows [which was meant to be a reply to your "deleted" query] evaluates the behavior of
MATCH("*",$A:$A,-1)
that you use within OFFSET. [ By the way, all this reminds me very much of the cautious thread on COUNTIF that Juan Pablo had initiated at the old board, which had ultimately the same goal: dynamically computing an alphanumeric range in a column. ]
Note: I'm copying verbatim my 'aborted reply' to your original query, which is very close to the current one.
=====================
I created in A1:A14 the following sample:
{"zax";"x";"w";"q";"d";"d";"c";"b";"aky";"a";"a";2;1;"b"}
[1]
=MATCH("*",A:A,1) results in #N/A.
[2]
=MATCH("*",A:A,0) results in 1.
[3]
=MATCH("*",A:A,-1) results in 14.
The sample above is unsorted.
The behavior of [1] is expected. I often exploit that behavior to determine the last row in a range filled up with duplicates:
1-Jan-02
1-Jan-02
1-Jan-02
within a dates range of say E2:E100 by
=MATCH("1-Jan-02"+0,E2:E100)
With the wild card (that is, "*") as lookup-value MATCH returns #N/A when it hits a blank cell in the range. [ This is a hypothesis. ]
The [2] returns 1 because * matches the first value in the range.
=MATCH("1-Jan-02"+0,E2:E100,0)
will indeed return the row number of the first 1-Jan-02 in E2:E100 in my date example.
The behavior of [3] is interesting in that it returns the row number of the last alpha valuenumeric value it finds in the range, even if the range is sorted ascending or descending or not at all. I think it is matching implicitly " " (a space char or an alpha char with the lowest ASCII code) to the alphanumeric values it comes across, failing to find one, it defaults to the row number of the last text value. If this could always be true, applying MATCH with match-type -1 would be useful. Alas.
Make " b" the last "b", it returns 11.
Make "!b" the last "b", it returns 11.
Now change "!b" to 2, it returns 11.
The result associated with the last three is correct. But, change the 2 (the latest change above) to sza, it still returns 11 instead of 14. At this point, MATCH as we apply it here, becomes unreliable. Maybe the change from alpha to numeric then back to alpha is the culprit in that the data type of the cell perhaps stays numeric, which MATCH with "*" rightly ignores.
Just experiment with " " alone in the first cell or second or third cell. All I get unreliable. I even got #N/A as result.
Recap. We don't still not have a reliable and efficient method of determining the row/column number of the last used cell in a column or row of the alphanumeric type as opposed to the numeric case.
This answers your:
"am I not able, therefore, to make a dynamic range name if the info isn't sorted in order ?"
if we paraphrase it as:
We can't use MATCH("*",A:A,-1) in order to create a (named) dynamic range regarding a column/row of alphanumeric type (that is, all text) as we can using MATCH(9.99999999999999E+307,A:A) where A is of numeric type.
Note. Often the volatile worksheet function COUNTA(A:A) is used, but that requires that no gaps in the data range in A occur.
==========================
Aladin