software

Good afternoon. I frequently need to group and count text data, and I'm
hoping some clever person out here knows of an easier/more robust/more
elegant way to do this.

I have one or more columns of quot;free text,quot; which needs to be collated to a
manageable number of groups. For example, the input may have entries such as
quot;Price,quot; quot;Cost,quot; quot;Pricing,quot; etc. These should all be grouped into quot;Price,quot;
and counted.

This is what I've been using. I consolidate the column(s) on a separate
worksheet, then use Advanced Filter to get unique values. These go in column
A. In column B I have COUNTIF functions to see how many times each original
value appears. In column C I type the category it goes into. In column D I
have a SUMIF, which sums column B where the value in C equals this category.
This function is nested in an IF and MATCH so that each category is only
counted once. This is what it looks like:

ABCD
Aardvark1Mammal13
Bear2Mammal
Cod5Fish8
Dog10Mammal
Eel3Fish
Fly7Insect7

The formulae in column D return null when the category has already been
displayed, which is why each category only appears once.
To chart this data, I copy columns C and D, paste their values, and sort
descending by D. Alternately, I'll copy and paste the categories and use
VLOOKUPs to find the total for each category; this doesn't have to be updated
if the raw data changes, as long as there are no new values.
It works, but is there a better way? Especially, is there some way that
would let me sort this block of data, so I don't need to copy values to a new
location? My boss has a slightly different solution with DSUMs, but it's
fragile and to my thinking even less elegant.
One idea I had would be to add a tiny fraction to the values in D based on
the text value in C. This would make each value in D unique, so I could
write a separate lookup table using LARGE and MATCH, and not have to update
it. Does this make sense?
Thanks for any opinions!

Maybe you could create a lookup table and assign codes...
then have your original table lookup the codes...

then manipulate the information to use your sumif or countif functions...

or you can just add columns for your various categories and use if
statements to populate those columns when there is a match between the cell
and the column header...
--
Tedquot;JonOfAllTradesquot; wrote:

gt; Good afternoon. I frequently need to group and count text data, and I'm
gt; hoping some clever person out here knows of an easier/more robust/more
gt; elegant way to do this.
gt;
gt; I have one or more columns of quot;free text,quot; which needs to be collated to a
gt; manageable number of groups. For example, the input may have entries such as
gt; quot;Price,quot; quot;Cost,quot; quot;Pricing,quot; etc. These should all be grouped into quot;Price,quot;
gt; and counted.
gt;
gt; This is what I've been using. I consolidate the column(s) on a separate
gt; worksheet, then use Advanced Filter to get unique values. These go in column
gt; A. In column B I have COUNTIF functions to see how many times each original
gt; value appears. In column C I type the category it goes into. In column D I
gt; have a SUMIF, which sums column B where the value in C equals this category.
gt; This function is nested in an IF and MATCH so that each category is only
gt; counted once. This is what it looks like:
gt;
gt; ABCD
gt; Aardvark1Mammal13
gt; Bear2Mammal
gt; Cod5Fish8
gt; Dog10Mammal
gt; Eel3Fish
gt; Fly7Insect7
gt;
gt; The formulae in column D return null when the category has already been
gt; displayed, which is why each category only appears once.
gt; To chart this data, I copy columns C and D, paste their values, and sort
gt; descending by D. Alternately, I'll copy and paste the categories and use
gt; VLOOKUPs to find the total for each category; this doesn't have to be updated
gt; if the raw data changes, as long as there are no new values.
gt; It works, but is there a better way? Especially, is there some way that
gt; would let me sort this block of data, so I don't need to copy values to a new
gt; location? My boss has a slightly different solution with DSUMs, but it's
gt; fragile and to my thinking even less elegant.
gt; One idea I had would be to add a tiny fraction to the values in D based on
gt; the text value in C. This would make each value in D unique, so I could
gt; write a separate lookup table using LARGE and MATCH, and not have to update
gt; it. Does this make sense?
gt; Thanks for any opinions!