Yet another Excel question

Factual Questions
#1

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?

#2

You want to make a pivot table. Have you worked with pivot tables before?

#3

Yup - a pivot table will crank that out instantly. Which version of Excel do you have?

#4

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)
#5

You could probably do it more easily with a histogram.

#6

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
  1. 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.
  2. Go to the “Insert” tab on the ribbon, click under “PivotTable” on the far left, and select “PivotTable”.
  3. Confirm that the table selected is correct, and that you’re placing it in a new worksheet.
  4. 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
#7

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.

#8

I’m on 2007, and I’m reading up on piviot tables right now.
What is a histogram? I’ve never heard the term.

#9

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.

#10

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.

#11

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.

#12

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.

#13

Got it, Thanks!