OK, I have a 21,000 row spreadsheet. (Yeah I know :eek:)
I have one column of information I am concerned with. I want to know how many times each discrete entry shows up on this list. For example some of the values in that column are 7500, 7500S, *, 15000, 15000S, etc. I want to know how many times each of those show up.
I know I could sort the values in the column and start counting, but with 21,000 rows, I don’t want to invest that kind of time.
So how can I do this?
You want to make a pivot table. Have you worked with pivot tables before?
Yup - a pivot table will crank that out instantly. Which version of Excel do you have?
A pivot table is the right answer but, just for the hell of it, here’s a way to do it with a formula. Assuming the values you are interested in are in column A, starting at row 1, put this in B1:
=COUNTIF($A:$A,A1)
You could probably do it more easily with a histogram.
If it’s 2007, here’s what we do. Let’s work with the following sample data:
A B C
1 Name Data 1 Data 2
2 Albert apples 5
3 Albert oranges 8
4 Brian apples 2
5 Candy oranges 9
6 Dan apples 4
7 Eddie oranges 5
8 Frank apples 8
9 Frank oranges 3
10 Frank pears 1
11 Frank grapes 7
- Highlight the table - I’d drag my mouse from A to C, since you have a ginormous data set to work from with your real file.
- Go to the “Insert” tab on the ribbon, click under “PivotTable” on the far left, and select “PivotTable”.
- Confirm that the table selected is correct, and that you’re placing it in a new worksheet.
- For what you want to do (count the instances of “apples” v. “oranges” v. “pears”, etc.), drag “Data 1” down to the “Row Labels” box, and then drag it again to the “Values” box.
That should do it, and bring up the following:
Row Labels Count of Data 1
apples 4
grapes 1
oranges 4
pears 1
(blank)
Grand Total 10
If you wanted to know how many times each name came up, you’d do the same step #4, but with the “Name” column, and get this:
Row Labels Count of Name
Albert 2
Brian 1
Candy 1
Dan 1
Eddie 1
Frank 4
(blank)
Grand Total 10
And where the pivot really gets value is when you plug in “Name” and “Data 1” for the Row Label, “Data 2” for the Value, and then change the value from Count to Sum (by clicking on “Count of Data 2”, selecting “Value Field Settings” and selecting “Sum”. Feel free to try it at home.
I’m on 2007, and I’m reading up on piviot tables right now.
What is a histogram? I’ve never heard the term.
Rick, the nice thing about pivot tables is that you can move things around on a whim without doing a thing to your data. Feel free to start one up and play around with dragging headers around, into different boxes, etc. It’s a really worry-free tool that you’ll get more out of the more you tinker with.
The thing about pivot tables is you’ve got to drag the right thing to the right spot. In order to get things in the data field, drag the dependent variable to the top left corner. Not the top, not the left, but the top-left.
Have fun.
OK, I have managed to create a pivot table and report showing the data I want.
I have two columns in the report, the data and the count. looks like this:
Col A Col B
094 3
093 5
* 1,587
15K 200
Now I want to sort the data so that is is in either ascending or descending order of Column B. So far that solution has eluded me.
If you really need it in that order, you can just copy the data and paste it into a new chart and sort from there.
Got it, Thanks!