Counting text in a column

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 hav
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

--
prom4
-----------------------------------------------------------------------
prom4x's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2901
View this thread: http://www.excelforum.com/showthread.php?threadid=48741

0
11/22/2005 9:23:20 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
554 Views

Similar Articles

[PageSpeed] 17

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" <prom4x.1yx37n_1132694701.7994@excelforum-nospam.com> wrote in
message news:prom4x.1yx37n_1132694701.7994@excelforum-nospam.com...
>
> 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?action=getinfo&userid=29010
> View this thread: http://www.excelforum.com/showthread.php?threadid=487412
>


0
bob.phillips1 (6510)
11/22/2005 9:35:48 PM
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?action=getinfo&userid=29010
> View this thread: http://www.excelforum.com/showthread.php?threadid=487412
> 
> 
0
GarysStudent (1572)
11/22/2005 9:36:10 PM
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" <prom4x.1yx37n_1132694701.7994@excelforum-nospam.com> wrote in 
message news:prom4x.1yx37n_1132694701.7994@excelforum-nospam.com...
>
> 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?action=getinfo&userid=29010
> View this thread: http://www.excelforum.com/showthread.php?threadid=487412
> 


0
ken.wright (2489)
11/22/2005 9:37:15 PM
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?
Ro

--
Ron Coderr
-----------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2141
View this thread: http://www.excelforum.com/showthread.php?threadid=48741

0
11/22/2005 9:38:18 PM
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?action=getinfo&userid=29010
View this thread: http://www.excelforum.com/showthread.php?threadid=487412

0
11/22/2005 9:40:17 PM
Then Pivot Table is the easiest way to go

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

Data>Pivot 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?action=getinfo&userid=21419
View this thread: http://www.excelforum.com/showthread.php?threadid=487412

0
11/22/2005 9:47:22 PM
Ron, you are a god! 
Thank you very much.


-- 
prom4x
------------------------------------------------------------------------
prom4x's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29010
View this thread: http://www.excelforum.com/showthread.php?threadid=487412

0
11/22/2005 10:02:32 PM
Ditto - Pivot table

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


"prom4x" <prom4x.1yx450_1132695918.979@excelforum-nospam.com> wrote in 
message news:prom4x.1yx450_1132695918.979@excelforum-nospam.com...
>
> 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?action=getinfo&userid=29010
> View this thread: http://www.excelforum.com/showthread.php?threadid=487412
> 


0
ken.wright (2489)
11/22/2005 10:08:58 PM
Reply:

Similar Artilces:

Saving As. . PRN file problem broken up columns..
When I "save as" a Formatted Text (space delimited) format the output is broken up. I have around 58 colums of data that need to be saved to a PRN file as a dataset for a visual basic program. When I save as a Formatted Text (space delimited), quit, then open in WordPad the data has been split and stacked on top of each other doubbleing the amount of rows. Because it is now out of order the data set does not work witth the intended porgram. Is there a way to increase the # of colums that are exported in the PRN format with Excel 2003? I learned recently that WordPad will wrap te...

Which columns are in what tables
I have been asked to provide a list of all columns in all tables in a database. I have the output from "select * from sys.tables" and "select * from sys.columns", but how do I know which columns are in what tables? Thanks Bob This should do it: SELECT TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS; -- Plamen Ratchev http://www.SQLStudio.com thing (someone@microsoft.com) writes: > I have been asked to provide a list of all columns in all tables in a > database. > I have the output from "sel...

Multiple Columns for printing
I have 2 columns of 80 rows of information that I would like to see "split up" into 3 sets of data across one page. How can I do this? TIA Mac Hi Mac, See http://www.mvps.org/dmcritchie/excel/snakecol.htm -- Kind Regards, Niek Otten Microsoft MVP - Excel "Mac" <anonymous@discussions.microsoft.com> wrote in message news:09b201c54bea$c5f0f2e0$a401280a@phx.gbl... >I have 2 columns of 80 rows of information that I would > like to see "split up" into 3 sets of data across one > page. How can I do this? > TIA > > Mac ...

Two columns one stacked
I am trying to create a chart that has three pieces of data (one objective and two actuals) and have them combined into two columns for specific areas. I'd like the objective to be one color and the actuals to be a stack of the two sets of actuals to show the detail between the two. I have been unsuccessful in creating such a chart. I'm not sure if this is even possible. I'm using Excel 2003. Thank you. See the second example at http://people.stfx.ca/bliengme/ExcelTips/Columns.htm best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ir...

Establishing most common word in an Excel column
Using Office 2003. In my weatherstation data, [very long listings over 12 months x 4 readings per day ], the wind's direction changes are listed as:- NORTH, NE,SE,SE,EAST,ENE,WSW,SOUTH,SE,EAST, NW, WNW, and so on. I would like to find a formula that will tell me the most prolific word in the column list. I hope that someone can help me here. Thanks and kind regards, Brymor Can there be moe than one word in a single cell? -- Gary''s Student - gsnu201001 "Brymor" wrote: > Using Office 2003. > In my weatherstation data, [very long listings ...

can I copy a column of email addresses, paste into email address?
I too have a list of email addresses in one column and would like to insert all addresses into one email for a mass mailing, is it possible to do without copy and pasting each email address in one at a time? I'd use this. Insert a new column to the right of the email address column. I'm gonna use A for my email column and B for my new column. Put this in B1: =A1 put this in B2: =B1&";"&A2 (Change the separator to what you need) And drag down the column. Copy that last cell and paste into your email to: field. Lizizfree wrote: > > I too have a list of e...

Counting Dates #2
I have 4 different cells that have dates in them C5, D5, E5, F5 and want to enter a formula in G5 that counts the dates. If there is a dat in C5 I want it to count 1, if there is a date in D5 I want it to coun another 1 and so on. How do I do this -- Message posted from http://www.ExcelForum.com Hi if these cells are either blank or have a date value try =COUNTA(C5:F5) >-----Original Message----- >I have 4 different cells that have dates in them C5, D5, E5, F5 and I >want to enter a formula in G5 that counts the dates. If there is a date >in C5 I want it to count 1, if there...

Help Importing Text Files
Hello, Here is my situation. I work for an electronics repair facility, a new company we are doing work for is sending jobs to us via FTP as .txt files. If you open these text files using notepad, they look like jumbled words. For example, 0000001111232Johnsmithbeverlyhillsca90210. What I need to do is tell Excel to import this file using this formula: the first 'xx' spaced are for the customer's ID number, the next 'xx' spaces are for the customer's first name, the next 'xx' spaces are for last name, etc. Here is what I tried so far: 1. Set column headin...

charts, cubes, text problems
Hello I am wanting to create a stacked chart (2 stacks) with a trendline (seperate figures), but it won't allow me to do that. I can only create a normal chart (ie. not stacked) with a trendline. Is there a way to do this in powerpoint / excel? Any suggestions? I need your help urgently. Thanks Siobhan - The problem is that Excel doesn't think you want a trendline of something that depends on more than one series, since the variations are lumped together. But you can fake it: Add the data in the worksheet, to get the stacked values, and plot these as regular unstacked seri...

Prompt to choose text in PowerPoint
Is it possible to add a prompt each time I open a custom template that prompts me to select from a list of text? We would like to add a document designation (e.g. Internal, Copyright, Restricted). We would like the user to be prompted automatically every time they create a new presentation to choose from the designations our company uses. There's no native way to do this but a vba programmer would be able to set up an addin or in 2007 a custom template that did this. -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint...

Text Box Data Validation
Having a bit of a problem with what should probably be a simple task. Outlined, what needs to be done is as follows: Text Box is initially locked to prevent accidental data changes User double clicks In double click event, text box is unlocked allowing data to be edited. In the BeforeUpdate event user is presented with a message box so they can confirm they want to change the data If the user selects "Yes", data is changed, focus is changed to next control and text box is locked. If the user selects "No", new data is discarded, focus is set to next control, and...

How do I get the last 7 characters of a no. to another column?
I need to get the last 7 characters of a number in one column to another column so I can sort them. The number is in the format 00-1234-12. Is it a number, or is it a text string? If the latter, try =RIGHT(A2,7) -- David Biddulph "Jennifer" <Jennifer @discussions.microsoft.com> wrote in message news:3533668C-1E17-49E3-BF07-50FCF677ADF9@microsoft.com... >I need to get the last 7 characters of a number in one column to another > column so I can sort them. The number is in the format 00-1234-12. Hope your data is in text format and not formatted to displ...

removal of text shadow ran amonk
How do I remove text shadow that is beneath each letter in each word? I changed slide design in which the text had shadows, and some slides' text title has shadow behind each each letter in each word, which is what I want. However, there is text shadow beneath each letter in each word as well and it's upside down, which is what I would like to remove. By the way, when I delete the text altogether and retype it, the same problem recurs. Thank you in advance for your assistance. Sounds like the shadow was applied to the placeholder on the slide master. You'll w...

Creating a contact group from text file
I got 10 e-mail addresses in a word text file can I somehow make it into an address group or contacts without having to open a card manualy for each one and typing name nickname e-mail etc.? thnx L. You need to make sure it is in the correct order, however, you can add commas between fields and import it as comma deliminated file. It is one of the options for importing. "ronba" <lironba@yahoo.com> wrote in message news:1c7e9546.0411091706.7c3adb96@posting.google.com... > I got 10 e-mail addresses in a word text file > can I somehow make it into an address group or c...

Text field database query problem
Im doing a query with multiple criteria on a database that has similar text items in one column. eg FM, FM3, FMO, or FMC6. If I query for FMC6 I obtain the expected number of records but if I use just FM in the query criteria I pull in all the additional FMxxx records. How do I go about limiting the records pulled in by the query. Ive tried using "FM" or 'FM' in the query but to no avail. Thanks "mike@multiline.com.au" wrote: > Im doing a query with multiple criteria on a database that has similar > text items in one column. eg FM, FM3, FMO, or FMC6....

counting a reoccuring pattern within column #2
you got me on the right track and i figured something out, but i'm not sure how elegant it is. not sure if you care, but here's what i did. i wrote an array formula. the pattern i was looking for was the nonconsecutive five-number pattern "4,1,3,2,4". I wanted to see how many times it appeared in a column. so, i copied and pasted that column five times across columns L through P deleting the top cell from each consecutive column in addition to those deleted from the former. so, column L had 45 cells, M had 44, N had 43, etc. then i wrote this array formula "=SUM(...

Excel 2007 text sorting problem
I apologize in advance for decidedly noob lingo... In short, Excel is doing a very odd sort of last name/first name columns in a worksheet containing data from two separate lists. Here's an example: Sandra H Bailey Ward Bailey B B BAILEY BARBARA BAILEY BARBARA BAILEY DEBBIE BAILEY ELEANOR BAILEY ELOISE BAILEY JAMES BAILEY JANET BAILEY JANET BAILEY JANET BAILEY JESSIE BAILEY JIMMIE BAILEY JOHN S BAILEY JON...

text box look up
I am using a userform with a textbox to look up certain surnames and give me the people with it, I have asked before about this but the problem I am having it is only i type the name smith in the box and it comes up with the first smith in the line. How do I get it to move on until I get the one I want? I have seen in some programs you type "smith, a " and it comes up with Alice Smith or something similar. Here is the code I have been using and have been given extra, can someone give me advise on how to set it up. Private Sub TextBox7_AfterUpdate() Sheets("PRODUCTS")...

Text function 12-11-09
i have a database which i need to extract the supplier name and they are not in same order for example RPO-09393-Supplier AAA 980-Supplier BBBBBB AHU-OIJ-JUH-Supplier CC As you can see the suppliers name are always at the back, how to build the formula just to extract the supplier name (different lenght) that always at the back. Thanks Howard =TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99)) "Howard" wrote: > i have a database which i need to extract the supplier name and they are not > in same order for example > > RPO-...

Copying/pasting text to text box and exporting as webpage
With Publisher 2003 when I copy text from a webpage and paste it into a text box in Publisher 2003, it shows up fine, and is in the same format as the original. But, when I try to use the "Publish to the Web" feature and create a webpage out of it, and see a preview of it in a web browser, the text runs off the edge of the page. How can I get the text to stay within bounds of the text box I created? I am trying to redo a series of webpages I created without having to type the content all over again. I would like to do this without turning the text into a "picture". ...

merge unformatted date with text
My original date column was formatted 02/01/71. I created a new column and created a custom date format; 020171. I would like to take this column and merge it together with the last 3 digits of a column where a ss# resides. This column is a text column. For example, the new column should look like 020171008. Can anyone help? I tried this based on your example and used the =Text (originaldate,mmddyy) function and used a =Right(ss#,3) to create a new column for the last three of the ss#. Then Concatenated the two fields resulting in 02171999. Good Luck! >-----Original Message...

Corrupted Text in Home page
In Money 2004 on "My Money Home Page" all of the text under the headings "Breaking News" and "Headlines from MSN Money" is corrupted. It looks to be a programming language like Visual Basic,,,for example: var PXCL=newDate(0);var PrModATR=0 et al. There are 15 to 20 lines of text like this under each heading. Any ideas on how to repair this? Thank you. Jeff I just tried Money 2004 myself and could not reproduce this. The "headlines from MSN Money" looked fine. By the way this is on Vista and IE 7. So, it may be due to your environment. What opera...

Import a Text File
Hello, I am currently using a transfer text macro to import a text file. This works great is the name of the text file is always the same. Is there a similar marco (or any other suggestion) that will allow me to choose the text file to be imported? This text files always contain the same information, just different names. Thanks for all your help. Kevin "mccallen60" wrote: > Hello, > > I am currently using a transfer text macro (Access 2007) to import a text file. This works great if the name of the text file is always the same. Is there a similar ...

keyboard shortcut to wrap a text in cell.
keyboard shortcut to wrap a text in cell. Nitin, ALT+Enter. Hope that helps. If this helped you, please click 'yes' at the bottom of the post where it asks if the post was helpful to you. huber57 "nitin kansal" wrote: > keyboard shortcut to wrap a text in cell. ...

Empty Rows and Columns
Is there a Macro (or how does one create one) that eliminates automatically empty rows and columns? Try ASAP Utilities, a free Excel Add-in available at www.asap-utilities.com Vaya con Dios, Chuck, CABGx3 "fak119" wrote: > Is there a Macro (or how does one create one) that eliminates automatically > empty rows and columns? ...