If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Harlan Grove wrote:
Alan Beban wrote... ... Cute. But I suspect the real reason you don't want to deal with it is that the so-called "efficiency" to which you and many programmer/developers sometimes refer often involves nanoseconds of difference that are totally irrelevant to most users in most applications; interesting to you for purposes of posting oneupmanship, but somewhat misleading for users generally. Fine. Then consider whether the MakeArray formula, =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTab le,1),0)-1) /COLUMNS(DataTable))+1,COLUMNS*(DataTable)) a single MATCH against the data range transformed into a 1D array, with the result adjusted by a division inside INT to return the row number, against the ArrayMatch formula, =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0 , -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(data Table)) first returning the cell address of the matching cell then using another call to fix the column offset. It's subjective whether the row index contortions of the MakeArray formula are more obscure than the column offset contortions of the 2 ArrayMatch formula. For that matter, you could also have used =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1 ),COLUMNS(DataTable)) which would have been a LOT simpler than either of the others. Simplicity is good. Both the MakeArray and the single ArrayMatch formulas involve no volatile function calls, so they won't cause Excel to prompt users to save any file containing them if users try to close such workbooks without making any changes. Your two ArrayMatch formula, due to OFFSET and INDIRECT calls, would cause such confusing prompts. Is that an acceptable user consideration? It's certainly more constructive, particularly the suggestion of =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1 ),COLUMNS(DataTable)) which is significantly faster than the one including the two ArrayMatch function calls that I originally posted. Would have been nice had you focused on the instructional value for the users in the first place, rather than just on stroking your ego. But then, there you go! By the way, the formula with the MakeArray function call seems to return an error if the data table exceeds 65536 elements (I haven't yet identified why; it might be fixable), while neither of the ArrayMatch formulas seems to--though they are slower. Alan Beban |
#22
|
|||
|
|||
Alan Beban wrote:
Harlan Grove wrote: . . .Then consider whether the MakeArray formula, =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTab le,1),0)-1) /COLUMNS(DataTable))+1,COLUMNS*(DataTable)) a single MATCH against the data range transformed into a 1D array, with the result adjusted by a division inside INT to return the row number, against the ArrayMatch formula, =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0 , -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(data Table)) first returning the cell address of the matching cell then using another call to fix the column offset. It's subjective whether the row index contortions of the MakeArray formula are more obscure than the column offset contortions of the 2 ArrayMatch formula. ...By the way, the formula with the MakeArray function call seems to return an error if the data table exceeds 65536 elements . . . . The problem is with the built-in INDEX function; it fails if the array or reference contains more than 65536 elements. Alan Beban |
#23
|
|||
|
|||
Alan Beban wrote...
.... The problem is with the built-in INDEX function; it fails if the array or reference contains more than 65536 elements. Are you sure there isn't a problem with 65,536 elements? I'd suspect it chokes after 65,535 elements. Which would argue in favor of using the COUNTIF function provided in another branch of this thread. It's one drawback is the volatile OFFSET call. It'd always recalc, but it'd be lots faster than even a single udf call. |
#24
|
|||
|
|||
Alan Beban wrote...
.... =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable), 1),COLUMNS(DataTable)) which is significantly faster than the one including the two ArrayMatch function calls that I originally posted. Would have been nice had you focused on the instructional value for the users in the first place, rather than just on stroking your ego. But then, there you go! Of course you could have offerred it too, if you had thought of it. But then, there you go! By the way, the formula with the MakeArray function call seems to return an error if the data table exceeds 65536 elements (I haven't yet identified why; it might be fixable), while neither of the ArrayMatch formulas seems to--though they are slower. Excel can't handle any arrays with more than 65,535 entries in either of 1 or 2 dimensions, as you should know. It's questionable whether anyone should try to use brute force matching on so many cells. It'd be slow even without any udfs or volatile functions. There are tasks for which indexed database searches would be far more appropriate than unindexed spreadsheet searches. If the OP has so many entries to search, the OP is being foolish using a spreadsheet for the task. However, if the OP is only searching a few hundred entries or fewer, your caveat provides completeness of specification but is of no practical relevance. |
#25
|
|||
|
|||
Alan Beban wrote...
.... =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable), 1),COLUMNS(DataTable)) which is significantly faster than the one including the two ArrayMatch function calls that I originally posted. Would have been nice had you focused on the instructional value for the users in the first place, rather than just on stroking your ego. But then, there you go! Of course you could have offerred it too, if you had thought of it. But then, there you go! By the way, the formula with the MakeArray function call seems to return an error if the data table exceeds 65536 elements (I haven't yet identified why; it might be fixable), while neither of the ArrayMatch formulas seems to--though they are slower. Excel can't handle any arrays with more than 65,535 entries in either of 1 or 2 dimensions, as you should know. It's questionable whether anyone should try to use brute force matching on so many cells. It'd be slow even without any udfs or volatile functions. There are tasks for which indexed database searches would be far more appropriate than unindexed spreadsheet searches. If the OP has so many entries to search, the OP is being foolish using a spreadsheet for the task. However, if the OP is only searching a few hundred entries or fewer, your caveat provides completeness of specification but is of no practical relevance. |
#26
|
|||
|
|||
Harlan Grove wrote:
Alan Beban wrote... ... The problem is with the built-in INDEX function; it fails if the array or reference contains more than 65536 elements. Are you sure there isn't a problem with 65,536 elements? I'd suspect it chokes after 65,535 elements. . . . Dim arr, i ReDim arr(1 To 65536) For i = 1 To 65536 arr(i) = i * 2 Next MsgBox Application.Index(arr, 65536) ----displays 131072 in xl2002. In xl2000 and earlier it fails on 5462 elements or greater. Alan Beban |
#27
|
|||
|
|||
Alan Beban wrote:
Harlan Grove wrote: Alan Beban wrote... ... The problem is with the built-in INDEX function; it fails if the array or reference contains more than 65536 elements. Are you sure there isn't a problem with 65,536 elements? I'd suspect it chokes after 65,535 elements. . . . Dim arr, i ReDim arr(1 To 65536) For i = 1 To 65536 arr(i) = i * 2 Next MsgBox Application.Index(arr, 65536) ----displays 131072 in xl2002. In xl2000 and earlier it fails on 5462 elements or greater. Alan Beban Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either xl2000 or xl2002). Alan Beban |
#28
|
|||
|
|||
Alan Beban wrote...
Are you sure there isn't a problem with 65,536 elements? I'd suspect it chokes after 65,535 elements. . . . Dim arr, i ReDim arr(1 To 65536) For i = 1 To 65536 arr(i) = i * 2 Next MsgBox Application.Index(arr, 65536) ----displays 131072 in xl2002. In xl2000 and earlier it fails on 5462 elements or greater. Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either xl2000 or xl2002). Not comparable. A:B is a range, not an array. But you're correct that INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and subsequent arguments and converts them to long integers. |
#29
|
|||
|
|||
Harlan Grove wrote:
Alan Beban wrote... Are you sure there isn't a problem with 65,536 elements? I'd suspect it chokes after 65,535 elements. . . . Dim arr, i ReDim arr(1 To 65536) For i = 1 To 65536 arr(i) = i * 2 Next MsgBox Application.Index(arr, 65536) ----displays 131072 in xl2002. In xl2000 and earlier it fails on 5462 elements or greater. Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either xl2000 or xl2002). Not comparable. A:B is a range, not an array. But you're correct that INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and subsequent arguments and converts them to long integers. It seems that the limitation on the VBA invocation of the INDEX function is a bit subtler; it is not limited by the number of the elements in the array (see arr1 below) but apparently by the number of elements in a dimension. Sub testIt3a() Dim arr1, arr2, arr3 Dim i As Long, j As Long Dim x, y, z '65536 rows, 2 columns ReDim arr1(1 To 65536, 1 To 2) For i = 1 To 65536: For j = 1 To 2 arr1(i, j) = i * 2 + j Next: Next '1 row, 65536 columns ReDim arr2(1 To 65536) For i = 1 To 65536 arr2(i) = i Next '1 row, 65537 columns ReDim arr3(1 To 65537) For i = 1 To 65537 arr3(i) = i Next x = Application.Index(arr1, 65536, 2) Debug.Print x '---returns 131074 y = Application.Index(arr2, 65536) Debug.Print y '---returns 65536 z = Application.Index(arr3, 65536) 'Type mismatch error End Sub Alan Beban |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Index layout problem | jacobk | General Discussion | 1 | June 24th, 2005 12:07 AM |
Complex LookUp / Match Problem ?? | carl | Worksheet Functions | 2 | May 2nd, 2005 08:53 PM |
INDEX & MATCH - Help please | litos_aldovea | General Discussion | 6 | June 17th, 2004 07:18 PM |
Error with Index + Match formula | Frank Kabel | Worksheet Functions | 0 | April 6th, 2004 05:49 PM |
vlookup? index? match? | annoyed | Worksheet Functions | 3 | March 18th, 2004 10:04 AM |