I used to think I was good at Excel until this stumped me.

I have a column with a variety of text in each cell.

Example:

A1
bob
bob
ted
ted
ted
charley
charley
charley
charley, etc.

Is there a formula or way I can take the whole column of data and have
it come back with something similar to:
bob = 2
ted=3
charley=4
etc.

for all the variables in the column?

Please help, I am dying over here.


--
prom4x
------------------------------------------------------------------------
prom4x's Profile: http://www.excelforum.com/member.php...o&userid=29010
View this thread: http://www.excelforum.com/showthread...hreadid=487412

In B1 add = A1
In C1 add =IF(B1="","",(" = "&COUNTIF(A:A,A1)))
In B2 add
=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

which is an array formula, so commit with Ctrl-Shift-Enter

Copy B2 down, and C1 down.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"prom4x" wrote in
message ...

I used to think I was good at Excel until this stumped me.

I have a column with a variety of text in each cell.

Example:

A1
bob
bob
ted
ted
ted
charley
charley
charley
charley, etc.

Is there a formula or way I can take the whole column of data and have
it come back with something similar to:
bob = 2
ted=3
charley=4
etc.

for all the variables in the column?

Please help, I am dying over here.


--
prom4x
------------------------------------------------------------------------
prom4x's Profile:
http://www.excelforum.com/member.php...o&userid=29010
View this thread: http://www.excelforum.com/showthread...hreadid=487412

I would use a Pivot table. You will need a label at the top of the column,
names, for example. Just put names in the row area and count of names in the
data area.

Once you have tried a Pivot Table, you will never go back.


--
Gary's Student


"prom4x" wrote:


I used to think I was good at Excel until this stumped me.

I have a column with a variety of text in each cell.

Example:

A1
bob
bob
ted
ted
ted
charley
charley
charley
charley, etc.

Is there a formula or way I can take the whole column of data and have
it come back with something similar to:
bob = 2
ted=3
charley=4
etc.

for all the variables in the column?

Please help, I am dying over here.


--
prom4x
------------------------------------------------------------------------
prom4x's Profile: http://www.excelforum.com/member.php...o&userid=29010
View this thread: http://www.excelforum.com/showthread...hreadid=487412

Data / Filter / Advanced Filter - Copy to another location gets you a unique
list. COUNTIF then gets you the number of each of the elements within the
source data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"prom4x" wrote in
message ...

I used to think I was good at Excel until this stumped me.

I have a column with a variety of text in each cell.

Example:

A1
bob
bob
ted
ted
ted
charley
charley
charley
charley, etc.

Is there a formula or way I can take the whole column of data and have
it come back with something similar to:
bob = 2
ted=3
charley=4
etc.

for all the variables in the column?

Please help, I am dying over here.


--
prom4x
------------------------------------------------------------------------
prom4x's Profile:
http://www.excelforum.com/member.php...o&userid=29010
View this thread: http://www.excelforum.com/showthread...hreadid=487412

If you want to use a formula:

try this:
=COUNTIF(A1:A10,"BOB")
or
=COUNTIF(A1:A10,"*BOB*")

(adjust the range references to suit your situation)

You could also us a Pivot Table

Does that help?
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=487412

There is about a hundred titles in this column. Is there a way to get a
count on each title in one shot?


--
prom4x
------------------------------------------------------------------------
prom4x's Profile: http://www.excelforum.com/member.php...o&userid=29010
View this thread: http://www.excelforum.com/showthread...hreadid=487412

Then Pivot Table is the easiest way to go

Make sure there is a column heading, like "FirstName", at the top of
the data.

DataPivot Table
\Use Excel list
\Select your list (including the heading)
\Click the [Layout] button and
Drag labels into the pivot table
ROW: FirstName
DATA: Count of FirstName

Select where you want the Pivot Table and....there's your name count
table.

Does that help?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=487412

Ron, you are a god!
Thank you very much.


--
prom4x
------------------------------------------------------------------------
prom4x's Profile: http://www.excelforum.com/member.php...o&userid=29010
View this thread: http://www.excelforum.com/showthread...hreadid=487412

Ditto - Pivot table

Regards
Ken..............


"prom4x" wrote in
message ...

There is about a hundred titles in this column. Is there a way to get a
count on each title in one shot?


--
prom4x
------------------------------------------------------------------------
prom4x's Profile:
http://www.excelforum.com/member.php...o&userid=29010
View this thread: http://www.excelforum.com/showthread...hreadid=487412