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
|
|||
|
|||
hash function for large strings
"Pete_UK" wrote...
this is what the final part of the string gets converted into: [Business_Type(¡¦£,¡¦_Equip¢, ¡¦_RE¢,¡§£, ¡§_Equip¢, ] .... OK, I screwed up. Sorry. This might shorten the OP's records enough, or it might not. Also, if there just happen to be more than 128 such common phrases, you'd need to alter your approach. I still believe a macro approach would be better than a udf approach, and a macro approach could prompt for selection of a different range containing a table of common susbtrings rather than hard coding them into a udf. |
#22
|
|||
|
|||
hash function for large strings
Pete_UK wrote...
Well, you could wait for Excel 12 later in the year, but if you want to .... Maybe you could wait for Excel 13 or 14, but this particular problem won't be fixed in Excel 12. David Gainer confirmed in the Excel 12 blog that VLOOKUP will remain limited to searching up to but no more than 255 chars in Excel 12. FWIW, Lotus 123 97 Edition (almost 10 years old) handles lookup values 255 characters, as does the Windows port of Gnumeric 1.6.0. I'd be surprised if Excel wasn't the only arguably high-end spreadsheet that can't handle such strings. Microsoft has a very bad tendency to leave matters of actual functionality unchanged version to version over decades but spend thousands of developers' hours on interface and other eyewash issues. Excel 12 is Microsoft's latest attempt to prove their assertion that they're years ahead of the competition. Then stuff like this comes along an proves that when low-level backwaters of functionality are at issue, it's Excel that's a decade or so behind the competition. And a long as the lemmings dutifully upgrade, there'll be no incentive for them to fix this. |
#23
|
|||
|
|||
hash function for large strings
Regarding the array solution that you proposed - I'm assuming that the large
string gets placed at the location of "x" in the following array formula: =INDEX(B1:B6,MATCH(TRUE,(A1:A6=x),0)) Was that your intent? - when I tried that, I got a "formula too long" error. "Harlan Grove" wrote: Pete_UK wrote... You won't be able to extend the above formula very much because of the limits on nesting. Here's an example UDF to do the same thing: Function reduce(test As String) As String reduce = test If Len(reduce) 255 Then Exit Function reduce = Application.WorksheetFunction.Substitute(reduce, "BT_Adds_Non_Recourse", Chr(161)) reduce = Application.WorksheetFunction.Substitute(reduce, "_Cost_Amt", Chr(162)) reduce = Application.WorksheetFunction.Substitute(reduce, "_Recv_Amt", Chr(163)) reduce = Application.WorksheetFunction.Substitute(reduce, "AC_IS_", Chr(164)) reduce = Application.WorksheetFunction.Substitute(reduce, "_Fixed", Chr(165)) reduce = Application.WorksheetFunction.Substitute(reduce, "_NB", Chr(166)) reduce = Application.WorksheetFunction.Substitute(reduce, "_EB", Chr(167)) reduce = Application.WorksheetFunction.Substitute(reduce, "Finance_Leases", Chr(168)) reduce = Application.WorksheetFunction.Substitute(reduce, "Conditional_Sales_Contracts", Chr(169)) End Function I've added a few extra functions to the end, which reduce the length of the original string to 184 characters when used as: =reduce(A1) The function does not change any original text which is already less than 255 characters in length. Obviously you can apply this to other strings by copying down, but you can also extend the UDF to cope with other patterns - just change the character number at the end of the line. And thinking holistically, how does this udf help? While it may reduce the length of the 1st argument to VLOOKUP, it's not usable directly for the first column of the 2nd argument to VLOOKUP. It's not even usable directly in an INDEX/MATCH approach because it can't handle range or array arguments and return values. At best, the OP would need to use one additional cell per log file record with each of those cells calling this UDF with the corresponding record value. Calling *any* udf a few thousand times even on a superfast system will bring back fond memories of the responsiveness of 110 baud dial-up mainframe connections. MATCH is afflicted with the same 256 character limit on comparisons as VLOOKUP. However, the = operator isn't so afflicted. Use INDEX/MATCH. Given A1:A6 containing =REPT("0123456789ABCDEF",16)&" - "&CHAR(64+ROW()) and B1:B6 containing =ROW() Then instead of VLOOKUP(x,A1:B6,2,0) use arrays formulas like =INDEX(B1:B6,MATCH(TRUE,(A1:A6=x),0)) A kludge, but this will be faster than any udf. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
determine if Excel is open | geebee | General Discussion | 11 | January 20th, 2006 09:25 PM |
How to modify function | Kevin | General Discussion | 5 | January 4th, 2006 10:12 PM |
SPELL CURRENCY FUNCTION | Rajesh | Using Forms | 1 | October 1st, 2005 11:04 PM |
Attaching Code | DS | General Discussion | 2 | August 22nd, 2005 11:21 PM |
Need a ISWorkday Function -- Any Ideas | Mark | Worksheet Functions | 5 | March 29th, 2005 01:58 AM |