Hatrack River Forum: OO Calc/MS Excel Function Question

This is topic OO Calc/MS Excel Function Question in forum Books, Films, Food and Culture at Hatrack River Forum.

To visit this topic, use this URL:
http://www.hatrack.com/ubb/main/ultimatebb.php?ubb=get_topic;f=2;t=041845

Posted by Swampjedi (Member # 7374) on :

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.

Posted by TomDavidson (Member # 124) on :

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.

Posted by xnera (Member # 187) on :

Pivot tables are your friends. [Smile]

I, too, vote pivot table in this instance. But I've also used the COUNTIF function for stuff like this, as Tom mentioned.

Posted by Swampjedi (Member # 7374) on :

[Angst]

You lost me at macro. Looks like I have some work to do.

I should have mentioned that I'm too poor for MS Office, so I use OO Calc.

Posted by TomDavidson (Member # 124) on :

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.

Posted by Swampjedi (Member # 7374) on :

Thanks, Tom. I just found that, myself.

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. [Razz]