Hi.

how do I create a list of words and count them as well using formula?
I have cell range (A1:M50) that contain words.

Thanks in advance.

In N1 enter:
=OFFSET(A$1,ROUNDUP(ROW()/13,0)-1,MOD(ROW()-1,13)) and copy down

this will produce a list of the contents of A1 thru M50 in a single column.

Let's assume that the cells in the table contain either a single word or are
blank. The count of the words is:

=COUNTIF(A1:M50,"<" & "")

--
Gary''s Student - gsnu200831


"milo" wrote:

Hi.

how do I create a list of words and count them as well using formula?
I have cell range (A1:M50) that contain words.

Thanks in advance.

I'm afraid that was not exactly what I mean.

Let say some cells in range (A1:M50) contained words "happy", "birthday",
"new year", "anniversary", etc.
All I wanted to do is to list down all the words in a separate area or
column. The result should be as follows:

1. anniversary (1)
2. birthday (1)
3. happy (5)
4. new year (1)

And if possible I want the result also sort automatically.

Thanks in advance.



"Gary''s Student" wrote in message
...
In N1 enter:
=OFFSET(A$1,ROUNDUP(ROW()/13,0)-1,MOD(ROW()-1,13)) and copy down

this will produce a list of the contents of A1 thru M50 in a single
column.

Let's assume that the cells in the table contain either a single word or
are
blank. The count of the words is:

=COUNTIF(A1:M50,"<" & "")

--
Gary''s Student - gsnu200831


"milo" wrote:

Hi.

how do I create a list of words and count them as well using formula?
I have cell range (A1:M50) that contain words.

Thanks in advance.

Once you have changed your 13x50 table into a single column, you can create a
Pivot Table to get EXACTLY what you want:

1. a separate table listing each item uniquely
2. alphabetized
3. a count of the item next to each item

see:
http://peltiertech.com/Excel/Pivots/pivottables.htm
and
http://www.contextures.com/xlPivot07.html#Unique

--
Gary''s Student - gsnu200831


"milo" wrote:

I'm afraid that was not exactly what I mean.

Let say some cells in range (A1:M50) contained words "happy", "birthday",
"new year", "anniversary", etc.
All I wanted to do is to list down all the words in a separate area or
column. The result should be as follows:

1. anniversary (1)
2. birthday (1)
3. happy (5)
4. new year (1)

And if possible I want the result also sort automatically.

Thanks in advance.



"Gary''s Student" wrote in message
...
In N1 enter:
=OFFSET(A$1,ROUNDUP(ROW()/13,0)-1,MOD(ROW()-1,13)) and copy down

this will produce a list of the contents of A1 thru M50 in a single
column.

Let's assume that the cells in the table contain either a single word or
are
blank. The count of the words is:

=COUNTIF(A1:M50,"<" & "")

--
Gary''s Student - gsnu200831


"milo" wrote:

Hi.

how do I create a list of words and count them as well using formula?
I have cell range (A1:M50) that contain words.

Thanks in advance.

Seems that using formula does not help very much on my problem. But anyway I
would like to thanks you for your suggestions and a very usefull
informations.