posted
This embarasses me. I have a graduate degress in Computer Science, and I can't use a spreadsheet.
This is what I'm trying to do. Say I have a list of text items. I want to extract the frequency of each items that appears in the list.
Example:
List: Dog Cat Dog Fish Cat Cat Rabbit
Output: Dog 2 Cat 3 Fish 1 Rabbit 1
Of course, this list is dynamic and has no set domain of acceptable values.
I cannot for the life of me figure this out. If I were doing SQL, this is a trivial query using group by and count. But in this strange cellular world, I can't seem to figure out how it works. Heck, I can't even extract the list of unique values.
Can anyone help me? I have hit Google, but can't seem to find anything at all helpful.
Posts: 1069 | Registered: Feb 2005
| IP: Logged |
posted
In most spreadsheets, you'd first need to run a macro or a separate function of some kind to identify the unique values. From there, you could count each instance using COUNTIF. If you don't need it to be pretty, you could use a COUNTIF(A:A,A1) function in Column B, where A1 holds "Dog" or whatever. That'll return all instances of "Dog." You could then autofill down column B. This will return all instances of each word FOR EACH WORD, which is not gorgeous but has the same function.
Alternately, if you're using Excel, you should convert this to a pivot table. One of the available options is "count," which in this case would give a "count each instance" result.
Posts: 37449 | Registered: May 1999
| IP: Logged |
posted
Pivot tables are your friends. I, too, vote pivot table in this instance. But I've also used the COUNTIF function for stuff like this, as Tom mentioned.
Posts: 1805 | Registered: Jun 1999
| IP: Logged |
posted
While I don't use OOCalc myself because I'm not too poor for MS Office (*grin*), I believe there's a similar feature in OOCalc called "DataPilot." DataPilot tables behave similarly to PivotTables, IIRC, and you might have some success there.
Posts: 37449 | Registered: May 1999
| IP: Logged |
And, there's more than just money keeping me from buying MSO. There's... pride. And principle. And that pride doesn't notice that I use Win2000 as an OS. Posts: 1069 | Registered: Feb 2005
| IP: Logged |
Printer-friendly view of this topic
![[Smile]](smile.gif)
I, too, vote pivot table in this instance. But I've also used the COUNTIF function for stuff like this, as Tom mentioned.
![[Angst]](graemlins/angst.gif)
You lost me at macro. Looks like I have some work to do.![[Razz]](tongue.gif)
