Prev: Print Dialog Box
Next: How do you do an IF statement in Excel comparing three columns
From: Brymor on 3 Feb 2010 06:55
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
From: Ashish Mathur on 3 Feb 2010 07:07
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" <Brymor(a)discussions.microsoft.com> wrote in message
news:1CEF1800-F196-4C7E-ACFC-96EFB7CB19FE(a)microsoft.com...
> 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

From: Brymor on 3 Feb 2010 07:17
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" <Brymor(a)discussions.microsoft.com> wrote in message
> news:1CEF1800-F196-4C7E-ACFC-96EFB7CB19FE(a)microsoft.com...
> > 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
>
>
> .
>
From: Brymor on 3 Feb 2010 07:21
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 <Bry...(a)discussions.microsoft.com> 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
>
> .
>
From: T. Valko on 3 Feb 2010 11:24
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Brymor" <Brymor(a)discussions.microsoft.com> wrote in message
news:FE35FA82-E1AC-47F4-BEEA-5BC04E58330A(a)microsoft.com...
> 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" <Brymor(a)discussions.microsoft.com> wrote in message
>> news:1CEF1800-F196-4C7E-ACFC-96EFB7CB19FE(a)microsoft.com...
>> > 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
>>
>>
>> .
>>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Print Dialog Box
Next: How do you do an IF statement in Excel comparing three columns