dget() on l_o_n_g text

[JBoulton]

Hi, All!

My table contains very long text in a NAME field (longest is 7
characters.) Many of the records are only slightly different from eac
other. Data/Filter/Advanced/Unique can differentiate between th
records but dget() and dsum() return values as though the function
only look at some limited number of characters in the text field.

Does anyone know if that's true and what the limit is?

I have completed the analysis by creating a unique key based on th
NAME and then using vlookup() to get the correct NAME back afterwards.
I had to use:

=mid(NAME,len(NAME)/2,6)&mid(NAME,len(NAME)/3,6)

TIA
Ji

 

[Harlan Grove]

My table contains very long text in a NAME field (longest is 77
characters.) Many of the records are only slightly different from each
other. Data/Filter/Advanced/Unique can differentiate between the
records but dget() and dsum() return values as though the functions
only look at some limited number of characters in the text field.

Does anyone know if that's true and what the limit is?

....

First, DGET was never a good idea as a function since it returns an error if
there are multiple records that match the criteria. An array INDEX/MATCH is
more flexible, robust and self-contained.

I've been toying with this, using cells containing several lines of garbage
ending with foo, and using the criterion *foo for that field. It looks like
255 characters is the effective max string length the D... functions
support.

The D... functions are hold-overs from the days when Excel had to mimick
Lotus 123 features, it uses the ancient 123 Release 2 syntax (meaning
separate criteria ranges), and it's unlikely Microsoft has expended any
resources on it in the last decade nor would ever do so again. In other
words, you should consider not using them at all. SUMPRODUCT constructs are
likely to prove more robust, and they're definitely more flexible.

 

[JBoulton]

Harlan,

Interesting info...

I wonder how many of *us* are holdovers from 123? I am. And, I'
always looking for ways to cull those thought processes.

Ji

 

[hgrove]

JBoulton wrote...
...

First, always include the original subject line in your responses i
excelforum. Maybe they're optional in excelforum, but omitting the
screws up treading in the Google Groups archive.

I wonder how many of *us* are holdovers from 123? I am.
And, I'm always looking for ways to cull those thought
processes.

I still use 123 on a daily basis - legacy systems S_L_O_W_L_Y bein
rewritten for Excel. Major PITA since these use 123 much better D..
function and query table capabilities intensively.

To repeat something I've written before: it's a good thing to use mor
than one spreadsheet program