Excel Question

BarkingGhostar

Diamond Member
Nov 20, 2009
5,639
54
126
#1
I have a spreadsheet where the first column as a unknown number of colors that appear many times over. I can sort them, by color, and know easy enough the variety of colors but I need a count of how many times a color appears. Mind you this is a spreadsheet with +100,000 rows and maybe three dozen or so different colors.

Example:

brown
red
orange
yellow
yellow
white
red
green
purtple
brown
etc.

I need to know how many times each color is present without having to use the filter list three dozen times. Any quick way of figuring this out?
 

JimKiler

Diamond Member
Oct 10, 2002
3,294
0
91
#2
Last edited:

BarkingGhostar

Diamond Member
Nov 20, 2009
5,639
54
126
#3
Sorry, I should have used something else, like apple, blueberry, cantaloupe, etc.
 

mpo

Senior member
Jan 8, 2010
347
0
81
#4
Pivot table will do this.

Select the column. Then select Pivot table.

When the dialog box comes up, drag the column heading to 'Rows', drag it again to 'Values'. The pivot table will give you a count of the number of times the individual color/fruit/etc type appears.

Row Labels Count of Color
brown 2
green 1
orange 1
purtple 1
red 2
white 1
yellow 2
Grand Total 10
 

BarkingGhostar

Diamond Member
Nov 20, 2009
5,639
54
126
#5
Thank you, sir, I just created my first test data set and used the Pivot tool function. I was lost on your description, but nifty--if not old--YouTube video demonstrated what you were suggesting and it worked. My test data set was only <100 rows and I want to apply this to something on the order of >100,000.
 

BarkingGhostar

Diamond Member
Nov 20, 2009
5,639
54
126
#7
Late last night I applied t to a data set with just over 170,000 rows with 33 unique entry types in said column and the Pivot table tool worked beautifully.
 

JimKiler

Diamond Member
Oct 10, 2002
3,294
0
91
#8
I love pivot tables. I need a shirt that says that. I can see the girls running toward me and screaming as i wear it, LOL.
 

Similar threads



ASK THE COMMUNITY

TRENDING THREADS