Text search / retrival functions
Posted by Tim on November 01, 2001 8:10 AM
I have some statistical samples that consist of a column of (non-unique) text references and a column of numbers. What I would like to do is produce a summary at the top of the sheet of a single line for each text reference - preferably without re-ordering the data (sorry - obscure reasons for that). So say the raw data looks like:
abc123 1.2
ssd245 2.0
abc123 4.5
etc
and I want a summary like
abc123 5.7 (total of all the abc123 rows)
ssd245 2.0
in real life I have just a little more data than that !
Any help much appreciated - I've tried combinations of array formulas and Min/Max type functions but don't seem to get anywhere as the references are text rather than numbers.
Posted by Todd on November 01, 2001 8:55 AM
Try something like this:
=VALUE(RIGHT(B14,LEN(B14)-6))
Posted by Gerry on November 01, 2001 10:37 AM
If you sort by your text column, then select all of your data, you can use Data-Subtotals to sum. Set subtotals to each change in the text column heading sum the value column.
Posted by Tim on November 02, 2001 1:02 AM
Sorry, I've not explained this very well. What I need is a text based function to produce a list of unique text entries from a list (unsorted) e.g.
abc123
bbf445
abc123
qwe123
abc123
so the list produce would be
abc123
bbf445
qwe123
is this possible?
Posted by Todd on November 02, 2001 4:41 AM
Tim,
Use the function I typed to extract the value, and use Left(b14,6) to extract the "abc123" part. Then use a pivot table. It will show you totals by abc123, etc.
