Where Is Chris Davison's MATCH Question?

[Aladin Akyurek]

Chris,

Did you delete the question while I was busy composing a lengthy answer?

If so, Why?



Aladin

[ This Message was edited by: Aladin Akyurek on 2002-03-17 16:14 ]

[Chris Davison]

Sorry Aladin, yes I did.

I realised what I was overlooking, so deleted it (obviously, not realising anyone was replying)

However, the conclusion I came to conflicts with my understanding of another syntax in another function, so I'm going to have to interrogate this further tonight after work, possibly reposting further.

Sorry to mess you around (and anyone else who was trying to reply to it)

cheers
Chris

[Chris Davison]

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

[Mark W.]

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

Yes, -1 is used only for arrays that are sorted in descending order. Furthermore, wildcards such as "*" can only be used with exact matches (match_type = 0).

Try using...

=OFFSET($A$1,,,MATCH(REPT("z",255),$A:$A))

[ This Message was edited by: Mark W. on 2002-03-19 12:57 ]

[Aladin Akyurek]

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

[Aladin Akyurek]

On 2002-03-19 12:50, Mark W. wrote:

Yes, -1 is used only for arrays that are sorted in descending order. Furthermore, wildcards such as "*" can only be used with exact matches (match_type = 0).

Try using...

=OFFSET($A$1,,,MATCH(REPT("z",255),$A:$A))

MATCH(REPT("z",255),$A:$A))

appears to compute the position (row/column number) of the last cell of alphanumeric type.

Given

{255 z's;"z";"q";"d";"c";"b";"aky";"x";"a";"~";"";3;2;1}

in A1:A14,

where A1 houses =REPT("z",255),

=MATCH(REPT("z",255),A:A) [1]

returns 11.

When you also put

=REPT("z",255)

in A8, [1] returns 8!

I believe the foregoing is too farfetched. So I consider [1] practically usable.

Well done.

Aladin

[Chris Davison]

a quick reply to acknowledge thanks

now to follow and understand

thanks
Chris

[Chris Davison]

Thanks for your input too Mark, I can see how this is more reliable.

I originally concluded that the help syntax :

Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.

could be read that the "z-a" was a catagory in itself : thus :

TRUE, FALSE, anytext,...2, 1, 0, -1, -2,..., and so on.

so *if* your column was just pure text, by definition, it was immaterial if it was sorted or not as there were no TRUES or numbers in the range to mess up the sort.

so far so good.

but, the smallest value that is greater than or equal to lookup_value of "*" didn't make sense. i couldn't see how this translated to the last row in the range.

Mistakenly assuming that it just *did*, I deleted the original post. Then however, I realised that this must also apply to VLOOKUP as the help syntax on TRUE is exactly the same :

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value.

I experimented with my John, Paul, George and Ringo range (all pure text unsorted) and got incorrect vlookup values, so my above logic was unsound, hence this post.

Dave, I was using the original offset formula from your very helpful dynamic range names example page, having recognised the potential of your excellent D functions examples. Can you add to this at all ?

many thanks
Chris

[Mark W.]

Chris, be advised that spaces (" "), null text strings (""), and some special characters (e.g., "-", ")", "&") wreak havoc on =MATCH("*",A:A,-1), but have no impact on =MATCH(REPT("z",255),A:A). Also, if A:A are text values of known or fixed length (i.e., account numbers, zip codes) you can reduce the number of REPT'd z's to only exceed that limit by 1 and; thus, improve efficiency.

[ This Message was edited by: Mark W. on 2002-03-19 15:27 ]

[Chris Davison]

I hear you Mark !

your formula appears to be more concise and reliable, I'll be applying it tommorrow. That original MATCH function within the OFFSET just threw me totally when I interrogated it.

thanks again




edit..... nice intuition too : my ranges will be columns of fixed-length text Tax codes, fixed-length text Tax systems,fixed-length text account codes etc etc... normal accounting system database output

[ This Message was edited by: Chris Davison on 2002-03-19 15:55 ]