Frequency counts in Excel

Started by bayonetbrant, December 03, 2013, 09:40:13 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

bayonetbrant

Per Jason's questions in the "pix for a laugh" thread, here's a "how-to" on getting frequency counts within Excel

Open your file and sort on the column you're counting in (counts1.png)

Move the ribbon to the "data" tab
Select all the data, then click "subtotal" on the right side of the "data" ribbon
(counts2.png)

"At each change" = the column you want the counts in
"Use function" = change from "sum" to "count"
"Add subtotal to" = the column you want the counts in
(counts3.png)

Marvel at your Excel-fu when the counts appear, by change in data point
(counts4.png)
The key to surviving this site is to not say something which ends up as someone's tag line - Steelgrave

"their citizens (all of them counted as such) glorified their mythology of 'rights'...and lost track of their duties. No nation, so constituted, can endure." Robert Heinlein, Starship Troopers

Mr. Bigglesworth

Shakes head at puny excel fu problem.
"Once more unto the breach, dear friends, once more; "
- Shakespeare's Henry V, Act III, 1598

JasonPratt

Certainly wasn't puny to me -- thanks muchly!
ICEBREAKER THESIS CHRONOLOGY! -- Victor Suvorov's Stalin Grand Strategy theory, in lots and lots of chronological order...
Dawn of Armageddon -- narrative AAR for Dawn of War: Soulstorm: Ultimate Apocalypse
Survive Harder! -- Two season narrative AAR, an Amazon Blood Bowl career.
PanzOrc Corpz Generals -- Fantasy Wars narrative AAR, half a combined campaign.
Khazâd du-bekâr! -- narrative dwarf AAR for LotR BfME2 RotWK campaign.
RobO Q Campaign Generator -- archived classic CMBB/CMAK tool!

Mr. Bigglesworth

You're kidding. I thought you were an accountant? Isn't excel one of your main programs?
"Once more unto the breach, dear friends, once more; "
- Shakespeare's Henry V, Act III, 1598

Staggerwing

Hel, I couldn't Excel my way out of a wet paper bag. This stuff is like Sanskrit  to me. I wish I had more time to learn because I use Numbers at work and I desperately want to implement a point of sale system that imports price and product data along with Rx values from an order entry form and then exports it to individual dispensing records that can be retained as medical records. Right now we use individual spreadsheets for everything and it makes me crazy to have to enter the same data into three different databases.
Vituð ér enn - eða hvat?  -Voluspa

Nothing really rocks and nothing really rolls and nothing's ever worth the cost...

"Don't you look at me that way..." -the Abyss
 
'When searching for a meaningful embrace, sometimes my self respect took second place' -Iggy Pop, Cry for Love

... this will go down on your permanent record... -the Violent Femmes, 'Kiss Off'-

"I'm not just anyone, I'm not just anyone-
I got my time machine, got my 'electronic dream!"
-Sonic Reducer, -Dead Boys

endfire79

A lot of people scoff at Excel, but it's still an impressive tool, and there's a lot that people that had no idea what they could really in Excel. I got mixed up with a lot of the interface changes from 2003 to 2007.  I usually knew what I wanted to do, but didn't know how excel forced you to do it (thank god for google). My wife works in supply chain management and surprised me with her knowledge.  Also is a wiz in Oracle & SAP ERP systems.
"I will return before you can say 'antidisestablishmentarianism'."

"A man may fight for many things. His country, his principles, his friends. The glistening tear on the cheek of a golden child. But personally, I'd mud-wrestle my own mother for a ton of cash, an amusing clock and a sack of French porn."

skeptical.platypus

Quote from: Staggerwing on December 04, 2013, 09:44:52 PM
Hel, I couldn't Excel my way out of a wet paper bag. This stuff is like Sanskrit  to me. I wish I had more time to learn because I use Numbers at work and I desperately want to implement a point of sale system that imports price and product data along with Rx values from an order entry form and then exports it to individual dispensing records that can be retained as medical records. Right now we use individual spreadsheets for everything and it makes me crazy to have to enter the same data into three different databases.

Staggerwing, it's possible the issue of entering the data from individual spreadsheets into three different databases can be accomplished through text delimited files. Basically, this is a fairly primitive but easy to learn/do process. Essentially, a text delimited file saves the values in your spreadsheet rows and columns as text, and uses "delimiters" like commas, periods, and colons to separate the data. Databases can then import the values. In your copious free time (everybody has that, right?), you might want to load up Excel and check out the help functions on exporting data as delimited text.

It's also quite possible that your databases can import from Excel directly.

The delimited text stuff may look/sound like sanskrit at first, but it's not.
The Law of Unintended Consequences, Seattle Pride Variant: The only city on the planet that can guarantee your purchase of recreational marijuana is from a stoner making $15/hr.

Staggerwing

Can the process be automated because, as noob as I am, I need to set the whole thing up so that other folks who make me look like Neo/Agent Smith can use it without actually learning anything, you know... New.
Vituð ér enn - eða hvat?  -Voluspa

Nothing really rocks and nothing really rolls and nothing's ever worth the cost...

"Don't you look at me that way..." -the Abyss
 
'When searching for a meaningful embrace, sometimes my self respect took second place' -Iggy Pop, Cry for Love

... this will go down on your permanent record... -the Violent Femmes, 'Kiss Off'-

"I'm not just anyone, I'm not just anyone-
I got my time machine, got my 'electronic dream!"
-Sonic Reducer, -Dead Boys

skeptical.platypus

The quick answer is, with XML, the answer is yes. Looking into XML will take up a considerably longer chunk of your free time.

The longer answer is, possibly. If the databases you are importing into have the same column names as your spreadsheets, it may be as simple as literally File Save As "delimited text" to create the file from excel. And then possibly as simple as an Import Text Delimited File command on your databases.

If the columns have different names from the spreadsheets or among each other, it's probably not worth the effort. You'd have to write fairly step by step instructions on what column names to change for which import.

Mind you, I don't quite know why you have a three step process -- order entry form, which is entered/imported into 1-3 excel spreadsheets, the results of which are then entered into three different databases -- and the reasons why you have that process are probably the devils in the detail that make whatever I'm speculating much closer to talking out of my arse than you deserve, lol.

As I'm sure you know, at least one of those steps could be cut out of the process. Your databases should be able to do the calculations that I assume you are using the spreadsheets for.
The Law of Unintended Consequences, Seattle Pride Variant: The only city on the planet that can guarantee your purchase of recreational marijuana is from a stoner making $15/hr.

Mr. Bigglesworth

Yes, delimited text is a handy trick. I used it when I was involved in migrating ERP systems. It's amazing how many old systems refuse to relinquish data in anything more modern.


Using the book example you could import a whole book to word, replace [period space] (at the end of each sentence) with [period space tab] or any other delimiter you like. Then import to most databases as tab or comma delimited to text field 255 characters. Any sentences too long that don't import will be sent to an error file to be decided on. A real database is better that excel which is an analysis package. So now for example Brant with his student papers in e format could compare sentences to see if any student is copying from the book. Or any book. You can do all kinds of data mining. You can convert the sentences into strings of numbers which take less room. You can make a database of the words. You can add grammar rules to the words, the list goes on. Excel list functions are fairly weak in comparison.
"Once more unto the breach, dear friends, once more; "
- Shakespeare's Henry V, Act III, 1598

skeptical.platypus

Quote from: endfire79 on December 04, 2013, 10:25:07 PM
A lot of people scoff at Excel, but it's still an impressive tool, and there's a lot that people that had no idea what they could really in Excel. I got mixed up with a lot of the interface changes from 2003 to 2007.  I usually knew what I wanted to do, but didn't know how excel forced you to do it (thank god for google). My wife works in supply chain management and surprised me with her knowledge.  Also is a wiz in Oracle & SAP ERP systems.

I felt the same pain, and am feeling it again. The menus/interface have changed with Office 365. Woot!
The Law of Unintended Consequences, Seattle Pride Variant: The only city on the planet that can guarantee your purchase of recreational marijuana is from a stoner making $15/hr.

JasonPratt

Quote from: Mr. Bigglesworth on December 04, 2013, 02:41:55 PM
You're kidding. I thought you were an accountant? Isn't excel one of your main programs?

No, we use a legacy program from back in the MacOS9 days (or earlier really) called MacP&L (formerly Accountant, Inc. when we originally bought it). An amazing little program, even with the payroll functions gutted by its last publisher (so they could use their own payroll programs instead). Still haven't found a cost-equivalent platform that would do the same things as thoroughly well. We need to, though, because new Macs don't even run old MacOS10.4, much less OS9.  :'( :'( :'( :'( :'( :'(

Why aren't there OS9 emulators for modern systems?! WAAHHH!!!

(True story, the company who technically owns the rights to MacP&L offered to sell us the source code for practically nothing. What the hell, I don't know what to do with that! But I bet someone could make a ton of money updating it to modern OS.)
ICEBREAKER THESIS CHRONOLOGY! -- Victor Suvorov's Stalin Grand Strategy theory, in lots and lots of chronological order...
Dawn of Armageddon -- narrative AAR for Dawn of War: Soulstorm: Ultimate Apocalypse
Survive Harder! -- Two season narrative AAR, an Amazon Blood Bowl career.
PanzOrc Corpz Generals -- Fantasy Wars narrative AAR, half a combined campaign.
Khazâd du-bekâr! -- narrative dwarf AAR for LotR BfME2 RotWK campaign.
RobO Q Campaign Generator -- archived classic CMBB/CMAK tool!