Code for counting number of word strings?

[Maria]

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!

 

[Bernard Liengme]

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/assistance/2002/articles/xlconPT101.aspx


best wishes