MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.