I have a long list of strings of words in an excel sheet and was wondering if
it possible to compile, in a new sheet, only one of each word string and a
column with the number of each word string that existed in the first list?

for example:

if I have a column in sheet 1 like this;
alfa beta
alfa beta
alfa beta
super
thank you
thank you

I would like to create two columns in sheet 2 that look like this;
alfa beta 3
super 1
thank you 2

What would the code for that be?

Thank you so much for your help! I really appriciate it!

Excel has a feature called a Pivot Table that will produce exactly what you
want. It can display a summary giving each unique item in the column and how
many times that item appears. See:

http://peltiertech.com/Excel/Pivots/pivottables.htm
--
Gary''s Student - gsnu200857


"Maria" wrote:

I have a long list of strings of words in an excel sheet and was wondering if
it possible to compile, in a new sheet, only one of each word string and a
column with the number of each word string that existed in the first list?

for example:

if I have a column in sheet 1 like this;
alfa beta
alfa beta
alfa beta
super
thank you
thank you

I would like to create two columns in sheet 2 that look like this;
alfa beta 3
super 1
thank you 2

What would the code for that be?

Thank you so much for your help! I really appriciate it!

First we need as list of the unique phrases.
Suppose you data is in A1:A100 of Sheet1
First insert a new row 1 and in A1 type a label such as MyText
Select all the data in column A; use Data | Filter | Advanced Filer and
specify Unique Only
Select and copy the result (including the lable in A1) and paste it on
Sheet2 starting in A1
Back on Sheet1 use Data | Filer | ShowAll to get the data back to original
state

In B1 of Sheet2 enter =COUNTIF(Sheet1!A:A,A2)
Copy down the column
_______________________________

Alternative: learn all about Pivot Table from one of these sites
Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx


best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Maria" wrote in message
...
I have a long list of strings of words in an excel sheet and was wondering
if
it possible to compile, in a new sheet, only one of each word string and a
column with the number of each word string that existed in the first list?

for example:

if I have a column in sheet 1 like this;
alfa beta
alfa beta
alfa beta
super
thank you
thank you

I would like to create two columns in sheet 2 that look like this;
alfa beta 3
super 1
thank you 2

What would the code for that be?

Thank you so much for your help! I really appriciate it!

One easy play is to create a pivot. Assume the col header is xxx. Select the
col, then create a pivot, drag n drop "xxx" into both the ROW and DATA areas
in layout. It'll appear as "Count of xxx" when you drop it into DATA. Click
OK Finish. Yup, thats it. The pivot will return exactly what you want, the
list of unique items in the ROW area, with the corresponding counts for each
item next to it, within the DATA area. Fast n easy, even with voluminous
source data.

Success ? Celebrate it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Maria" wrote:
I have a long list of strings of words in an excel sheet and was wondering if
it possible to compile, in a new sheet, only one of each word string and a
column with the number of each word string that existed in the first list?

for example:

if I have a column in sheet 1 like this;
alfa beta
alfa beta
alfa beta
super
thank you
thank you

I would like to create two columns in sheet 2 that look like this;
alfa beta 3
super 1
thank you 2

What would the code for that be?

Thank you so much for your help! I really appriciate it!