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.

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.

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.