Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Establishing most common word in an Excel column

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Establishing most common word in an Excel column

With your data in the range A2:A13, this array formula** returns SE as the
most frequent TEXT entry:

=INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Note that if there are an equal number of entries for more than one
direction the formula will return the direction that appears first from top
to bottom. For example:

NE
NE
SW
SW
SSW

The formula result will be NE. Both NE and SW appear the most often but NE
appears first in the list.

--
Biff
Microsoft Excel MVP


"Brymor" wrote in message
...
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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 897
Default Establishing most common word in an Excel column

In addition to what Biff suggested, perhaps a Pivot Table would also
produce what you need?

http://www.contextures.com/xlPivot05.html#TopItems

--JP

On Feb 2, 11:30*am, Brymor wrote:
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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Establishing most common word in an Excel column

On Feb 2, 9:30*pm, Brymor wrote:
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


You can also use Countif. Assuming your data is in Column A rows 1 to
24000 then in column B if you enter =COUNTIF($B$2:$B$24000, B2) and
drag it through the column the number of instances a particular value
repeats would be listed you can then choose for the largest number to
see which direction the wind was most of the times.

Hope that helps,
Anand
9910548139


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default Establishing most common word in an Excel column

Gary, tks for your response.
There are only random repeats of the sixteen different compas points, form
NORTH around to NNW. So, each abbreviation can be classed as one word in each
cell
--
Thanks and kind regards,
Brymor


"Gary''s Student" wrote:

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Establishing most common word in an Excel column

Hi,

Assume that your data in in range A2:A20000 (the heading is in A2). In B3,
type =countif(A$3:A20000,A3) and copy till B20000. In B2, type condition.
In E2, type condition and in E3, type =max(B3:B20000)

Select cell G2 and go to Data Advanced Filter Copy to another location.
In list range, select A2:B20000. In criteria range, select E2:E3. In Copy,
select G2. Check the box for unique records only. Click on OK

This should get you the desired result.

Please note that if the data changed, you will have to rerun the advanced
filter.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Brymor" wrote in message
...
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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default Establishing most common word in an Excel column

Many thanks Biff, works a treat!
Just copied / pasted your formula into cell, changed the range details and
BINGO, I got what I wanted.
--
Thanks and kind regards,
Brymor


"T. Valko" wrote:

With your data in the range A2:A13, this array formula** returns SE as the
most frequent TEXT entry:

=INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Note that if there are an equal number of entries for more than one
direction the formula will return the direction that appears first from top
to bottom. For example:

NE
NE
SW
SW
SSW

The formula result will be NE. Both NE and SW appear the most often but NE
appears first in the list.

--
Biff
Microsoft Excel MVP


"Brymor" wrote in message
...
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



.

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default Establishing most common word in an Excel column

Thanks JP,
Biff's suggestion worked OK, but will also try your suggestion, once I've
learnt about Pivot tables.
Wish me Luck!
--
Thanks and kind regards,
Brymor


"JP" wrote:

In addition to what Biff suggested, perhaps a Pivot Table would also
produce what you need?

http://www.contextures.com/xlPivot05.html#TopItems

--JP

On Feb 2, 11:30 am, Brymor wrote:
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


.

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Establishing most common word in an Excel column

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Brymor" wrote in message
...
Many thanks Biff, works a treat!
Just copied / pasted your formula into cell, changed the range details and
BINGO, I got what I wanted.
--
Thanks and kind regards,
Brymor


"T. Valko" wrote:

With your data in the range A2:A13, this array formula** returns SE as
the
most frequent TEXT entry:

=INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Note that if there are an equal number of entries for more than one
direction the formula will return the direction that appears first from
top
to bottom. For example:

NE
NE
SW
SW
SSW

The formula result will be NE. Both NE and SW appear the most often but
NE
appears first in the list.

--
Biff
Microsoft Excel MVP


"Brymor" wrote in message
...
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



.





  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 897
Default Establishing most common word in an Excel column

Good luck to you!

--JP

On Feb 3, 7:21*am, Brymor wrote:
Thanks JP,
Biff's suggestion worked OK, but will also try your suggestion, once I've
learnt about Pivot tables.
Wish me Luck!
--
Thanks and kind regards,
Brymor

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Determine if 2 cells share a common word paul c Excel Worksheet Functions 2 January 14th 10 04:52 AM
Stumped on establishing web querries on MS Excel Chris Excel Discussion (Misc queries) 9 August 16th 09 04:23 PM
In Excel 07, how do I merge two sprdshts with a common column? aleve21 Excel Worksheet Functions 1 May 6th 09 05:32 PM
Establishing Excel default toolbars JP Excel Discussion (Misc queries) 1 May 8th 08 12:48 AM
what three features in word,excel and powerpoint have n common? collegemom2005 New Users to Excel 1 June 11th 05 09:54 PM


All times are GMT +1. The time now is 05:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"