Most Frequently used Text value in a column

I have an Excel 2000 spreadsheet used for daily entries for any given month.
Some of the columns have text values instead of numeric.  It is possible
that the text entries would be the same for every day or they could vary
between 2 or 3 different text values.  I would like to know if there is a
formula that would retrieve the most frequently entered TEXT.

Ex:

Jan 01          cat
Jan 02          dog
Jan 03          cat
Jan 04          dog
Jan 05          dog
Jan 06          dog
Jan 07          cat
Jan 08          dog
Jan 09          bird
Jan 10          cat
=============
Max            dog

"dog" should be retrieved because its frequency was 5 while "cat" occurred 4
times and "bird" only once.

Thanks,
Ron


0
news
1/14/2004 4:30:54 PM
excel 39879 articles. 2 followers. Follow

9 Replies
583 Views

Similar Articles

[PageSpeed] 16

Hi Ron

try the following
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),
0),1)

you have to enter this as array formula (CTRL+SHIFT+ENTER)

HTH
Frank


0
frank.kabel (11126)
1/14/2004 5:06:49 PM
One way

=INDEX(B1:B10,MODE(MATCH(B1:B10,B1:B10,0)))


where B1:B10 holds your animals

-- 

Regards,

Peo Sjoblom


"news.newsguy.com" <idontknow@questioning> wrote in message
news:bu3qrv0ie7@enews1.newsguy.com...
> I have an Excel 2000 spreadsheet used for daily entries for any given
month.
> Some of the columns have text values instead of numeric.  It is possible
> that the text entries would be the same for every day or they could vary
> between 2 or 3 different text values.  I would like to know if there is a
> formula that would retrieve the most frequently entered TEXT.
>
> Ex:
>
> Jan 01          cat
> Jan 02          dog
> Jan 03          cat
> Jan 04          dog
> Jan 05          dog
> Jan 06          dog
> Jan 07          cat
> Jan 08          dog
> Jan 09          bird
> Jan 10          cat
> =============
> Max            dog
>
> "dog" should be retrieved because its frequency was 5 while "cat" occurred
4
> times and "bird" only once.
>
> Thanks,
> Ron
>
>


0
terre08 (1112)
1/14/2004 5:10:26 PM
Hi Peo
i'm impressed! Never used MODE in combination with MATCH before. This
is definetly shorter than my approach and does not require an array
formula

Regards
Frank

Peo Sjoblom wrote:
> One way
>
> =INDEX(B1:B10,MODE(MATCH(B1:B10,B1:B10,0)))
>
>
> where B1:B10 holds your animals
>
>
> "news.newsguy.com" <idontknow@questioning> wrote in message
> news:bu3qrv0ie7@enews1.newsguy.com...
>> I have an Excel 2000 spreadsheet used for daily entries for any
>> given month. Some of the columns have text values instead of
>> numeric.  It is possible that the text entries would be the same for
>> every day or they could vary between 2 or 3 different text values.
>> I would like to know if there is a formula that would retrieve the
>> most frequently entered TEXT.
>>
>> Ex:
>>
>> Jan 01          cat
>> Jan 02          dog
>> Jan 03          cat
>> Jan 04          dog
>> Jan 05          dog
>> Jan 06          dog
>> Jan 07          cat
>> Jan 08          dog
>> Jan 09          bird
>> Jan 10          cat
>> =============
>> Max            dog
>>
>> "dog" should be retrieved because its frequency was 5 while "cat"
>> occurred 4 times and "bird" only once.
>>
>> Thanks,
>> Ron


0
frank.kabel (11126)
1/14/2004 5:25:29 PM
Thanks for the response that helped a lot.
What suggestion would you have for a change to the formula if there was no
entry for a given day.

Ex:
Jan 01          cat
Jan 02          dog
Jan 03          cat
Jan 04          dog
Jan 05          dog
Jan 06          dog
Jan 07
Jan 08          dog
Jan 09          bird
Jan 10          cat
 =============
Max            dog

The current formula returns a #N/A error.

Thanks,
Ron


"Peo Sjoblom" <terre08@mvp.org> wrote in message
news:OqWhRFs2DHA.2604@TK2MSFTNGP09.phx.gbl...
> One way
>
> =INDEX(B1:B10,MODE(MATCH(B1:B10,B1:B10,0)))
>
>
> where B1:B10 holds your animals
>
> -- 
>
> Regards,
>
> Peo Sjoblom
>
>
> "news.newsguy.com" <idontknow@questioning> wrote in message
> news:bu3qrv0ie7@enews1.newsguy.com...
> > I have an Excel 2000 spreadsheet used for daily entries for any given
> month.
> > Some of the columns have text values instead of numeric.  It is possible
> > that the text entries would be the same for every day or they could vary
> > between 2 or 3 different text values.  I would like to know if there is
a
> > formula that would retrieve the most frequently entered TEXT.
> >
> > Ex:
> >
> > Jan 01          cat
> > Jan 02          dog
> > Jan 03          cat
> > Jan 04          dog
> > Jan 05          dog
> > Jan 06          dog
> > Jan 07          cat
> > Jan 08          dog
> > Jan 09          bird
> > Jan 10          cat
> > =============
> > Max            dog
> >
> > "dog" should be retrieved because its frequency was 5 while "cat"
occurred
> 4
> > times and "bird" only once.
> >
> > Thanks,
> > Ron
> >
> >
>
>


0
news
1/15/2004 3:17:06 PM
You could add this to the formula

=INDEX(B1:B10,MODE(IF(B1:B10<>"",MATCH(B1:B10,B1:B10,0))))

although now it has to be entered with ctrl + shift & enter, or you could
use
Frank's array formula

=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0),1)

-- 

Regards,

Peo Sjoblom


"news.newsguy.com" <idontknow@questioning> wrote in message
news:bu6atm01nrc@enews1.newsguy.com...
> Thanks for the response that helped a lot.
> What suggestion would you have for a change to the formula if there was no
> entry for a given day.
>
> Ex:
> Jan 01          cat
> Jan 02          dog
> Jan 03          cat
> Jan 04          dog
> Jan 05          dog
> Jan 06          dog
> Jan 07
> Jan 08          dog
> Jan 09          bird
> Jan 10          cat
>  =============
> Max            dog
>
> The current formula returns a #N/A error.
>
> Thanks,
> Ron
>
>
> "Peo Sjoblom" <terre08@mvp.org> wrote in message
> news:OqWhRFs2DHA.2604@TK2MSFTNGP09.phx.gbl...
> > One way
> >
> > =INDEX(B1:B10,MODE(MATCH(B1:B10,B1:B10,0)))
> >
> >
> > where B1:B10 holds your animals
> >
> > -- 
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "news.newsguy.com" <idontknow@questioning> wrote in message
> > news:bu3qrv0ie7@enews1.newsguy.com...
> > > I have an Excel 2000 spreadsheet used for daily entries for any given
> > month.
> > > Some of the columns have text values instead of numeric.  It is
possible
> > > that the text entries would be the same for every day or they could
vary
> > > between 2 or 3 different text values.  I would like to know if there
is
> a
> > > formula that would retrieve the most frequently entered TEXT.
> > >
> > > Ex:
> > >
> > > Jan 01          cat
> > > Jan 02          dog
> > > Jan 03          cat
> > > Jan 04          dog
> > > Jan 05          dog
> > > Jan 06          dog
> > > Jan 07          cat
> > > Jan 08          dog
> > > Jan 09          bird
> > > Jan 10          cat
> > > =============
> > > Max            dog
> > >
> > > "dog" should be retrieved because its frequency was 5 while "cat"
> occurred
> > 4
> > > times and "bird" only once.
> > >
> > > Thanks,
> > > Ron
> > >
> > >
> >
> >
>
>


0
terre08 (1112)
1/15/2004 4:38:37 PM
Hopefully I'm not being a pest but here's another question concerning this
formula and a little more background info.

The spreadsheet for which I need this formula is used in the following
manner.
Column A is the day/date of the month and the other columns are used for
flows or other operational data.  I have a couple of columns in which the
user will enter the name of a chemical added to a process. Under the row for
the last day of the month is the AVERAGE row.  I can average the numeric
entries with no problem.  I wish to retrieve the chemical name used most
frequently during the month in this AVERAGE cell for that column.

It may be that no chemicals were used and the Average cell should be blank.
Multiple chemicals could be used and the most frequently used (or in the
case of a tie the first chemical listed) would be the value in the Average
cell.  Any given day could have either a chemical name entered or be blank.

I can modify your formula to cover the instance where no entries are made so
that the AVERAGE cell is empty.

=IF(COUNTA(A1:A10)=0,"",INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,
0)))))
But if there is only 1 entry for the month then the formula returns the #N/A
error.  If there are at least 2 entries then it works.
Is there anyway to find and return the value for a single entry in a column
when the other cells are empty?


Frank's array formula works for either all empty or a single entry but if
the first cell (B1) is empty then it doesn't return anything regardless of
how many other entries were made.  Is there anyway to fix that?
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0),1)

I do appreciate the help from both of you.
Ron


"Peo Sjoblom" <terre08@mvp.org> wrote in message
news:upZTJY42DHA.556@TK2MSFTNGP11.phx.gbl...
> You could add this to the formula
>
> =INDEX(B1:B10,MODE(IF(B1:B10<>"",MATCH(B1:B10,B1:B10,0))))
>
> although now it has to be entered with ctrl + shift & enter, or you could
> use
> Frank's array formula
>
>
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0),1)
>
> -- 
>
> Regards,
>
> Peo Sjoblom
>
>
> "news.newsguy.com" <idontknow@questioning> wrote in message
> news:bu6atm01nrc@enews1.newsguy.com...
> > Thanks for the response that helped a lot.
> > What suggestion would you have for a change to the formula if there was
no
> > entry for a given day.
> >
> > Ex:
> > Jan 01          cat
> > Jan 02          dog
> > Jan 03          cat
> > Jan 04          dog
> > Jan 05          dog
> > Jan 06          dog
> > Jan 07
> > Jan 08          dog
> > Jan 09          bird
> > Jan 10          cat
> >  =============
> > Max            dog
> >
> > The current formula returns a #N/A error.
> >
> > Thanks,
> > Ron
> >
> >
> > "Peo Sjoblom" <terre08@mvp.org> wrote in message
> > news:OqWhRFs2DHA.2604@TK2MSFTNGP09.phx.gbl...
> > > One way
> > >
> > > =INDEX(B1:B10,MODE(MATCH(B1:B10,B1:B10,0)))
> > >
> > >
> > > where B1:B10 holds your animals
> > >
> > > -- 
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > >
> > > "news.newsguy.com" <idontknow@questioning> wrote in message
> > > news:bu3qrv0ie7@enews1.newsguy.com...
> > > > I have an Excel 2000 spreadsheet used for daily entries for any
given
> > > month.
> > > > Some of the columns have text values instead of numeric.  It is
> possible
> > > > that the text entries would be the same for every day or they could
> vary
> > > > between 2 or 3 different text values.  I would like to know if there
> is
> > a
> > > > formula that would retrieve the most frequently entered TEXT.
> > > >
> > > > Ex:
> > > >
> > > > Jan 01          cat
> > > > Jan 02          dog
> > > > Jan 03          cat
> > > > Jan 04          dog
> > > > Jan 05          dog
> > > > Jan 06          dog
> > > > Jan 07          cat
> > > > Jan 08          dog
> > > > Jan 09          bird
> > > > Jan 10          cat
> > > > =============
> > > > Max            dog
> > > >
> > > > "dog" should be retrieved because its frequency was 5 while "cat"
> > occurred
> > > 4
> > > > times and "bird" only once.
> > > >
> > > > Thanks,
> > > > Ron
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
news
1/15/2004 8:35:34 PM
news.newsguy.com wrote:
[snip]
> Frank's array formula works for either all empty or a single entry
> but if the first cell (B1) is empty then it doesn't return anything
> regardless of how many other entries were made.  Is there anyway to
> fix that?
>
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),
0),1)

Hi
I tried this formula again and i had no problem if B1 is empty. If you
like, send me your spreadsheet and I will have a look at it

Frank

0
frank.kabel (11126)
1/15/2004 9:22:54 PM
Frank's formula works if the first cell is empty.. Regardless the reason the
error with one value only is the mode function,
try to put single number in a range and use mode

=MODE(A1:A10)

mode needs at least 2 values, so you have to condition that before the mode
part, also note that mode will return an error
if all values are unique so if you have 2 entries and they are "x" and "y"
mode will retrain an error even if you would dodge the single entry with
something like

=IF(COUNTA(A1:A10)=0,"",IF(COUNTA(A1:A10)=1,INDEX(A1:A10,MATCH(TRUE,A1:A10<>
"",0)),INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))))))

However I would use this

=IF(COUNTA(A1:A10)=0,"",INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNT
IF(A1:A10,A1:A10),0),1))

array entered

-- 

Regards,

Peo Sjoblom


"news.newsguy.com" <idontknow@questioning> wrote in message
news:bu6tip02uan@enews1.newsguy.com...
> Hopefully I'm not being a pest but here's another question concerning this
> formula and a little more background info.
>
> The spreadsheet for which I need this formula is used in the following
> manner.
> Column A is the day/date of the month and the other columns are used for
> flows or other operational data.  I have a couple of columns in which the
> user will enter the name of a chemical added to a process. Under the row
for
> the last day of the month is the AVERAGE row.  I can average the numeric
> entries with no problem.  I wish to retrieve the chemical name used most
> frequently during the month in this AVERAGE cell for that column.
>
> It may be that no chemicals were used and the Average cell should be
blank.
> Multiple chemicals could be used and the most frequently used (or in the
> case of a tie the first chemical listed) would be the value in the Average
> cell.  Any given day could have either a chemical name entered or be
blank.
>
> I can modify your formula to cover the instance where no entries are made
so
> that the AVERAGE cell is empty.
>
>
=IF(COUNTA(A1:A10)=0,"",INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,
> 0)))))
> But if there is only 1 entry for the month then the formula returns the
#N/A
> error.  If there are at least 2 entries then it works.
> Is there anyway to find and return the value for a single entry in a
column
> when the other cells are empty?
>
>
> Frank's array formula works for either all empty or a single entry but if
> the first cell (B1) is empty then it doesn't return anything regardless of
> how many other entries were made.  Is there anyway to fix that?
>
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0),1)
>
> I do appreciate the help from both of you.
> Ron
>
>
> "Peo Sjoblom" <terre08@mvp.org> wrote in message
> news:upZTJY42DHA.556@TK2MSFTNGP11.phx.gbl...
> > You could add this to the formula
> >
> > =INDEX(B1:B10,MODE(IF(B1:B10<>"",MATCH(B1:B10,B1:B10,0))))
> >
> > although now it has to be entered with ctrl + shift & enter, or you
could
> > use
> > Frank's array formula
> >
> >
>
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0),1)
> >
> > -- 
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "news.newsguy.com" <idontknow@questioning> wrote in message
> > news:bu6atm01nrc@enews1.newsguy.com...
> > > Thanks for the response that helped a lot.
> > > What suggestion would you have for a change to the formula if there
was
> no
> > > entry for a given day.
> > >
> > > Ex:
> > > Jan 01          cat
> > > Jan 02          dog
> > > Jan 03          cat
> > > Jan 04          dog
> > > Jan 05          dog
> > > Jan 06          dog
> > > Jan 07
> > > Jan 08          dog
> > > Jan 09          bird
> > > Jan 10          cat
> > >  =============
> > > Max            dog
> > >
> > > The current formula returns a #N/A error.
> > >
> > > Thanks,
> > > Ron
> > >
> > >
> > > "Peo Sjoblom" <terre08@mvp.org> wrote in message
> > > news:OqWhRFs2DHA.2604@TK2MSFTNGP09.phx.gbl...
> > > > One way
> > > >
> > > > =INDEX(B1:B10,MODE(MATCH(B1:B10,B1:B10,0)))
> > > >
> > > >
> > > > where B1:B10 holds your animals
> > > >
> > > > -- 
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > >
> > > > "news.newsguy.com" <idontknow@questioning> wrote in message
> > > > news:bu3qrv0ie7@enews1.newsguy.com...
> > > > > I have an Excel 2000 spreadsheet used for daily entries for any
> given
> > > > month.
> > > > > Some of the columns have text values instead of numeric.  It is
> > possible
> > > > > that the text entries would be the same for every day or they
could
> > vary
> > > > > between 2 or 3 different text values.  I would like to know if
there
> > is
> > > a
> > > > > formula that would retrieve the most frequently entered TEXT.
> > > > >
> > > > > Ex:
> > > > >
> > > > > Jan 01          cat
> > > > > Jan 02          dog
> > > > > Jan 03          cat
> > > > > Jan 04          dog
> > > > > Jan 05          dog
> > > > > Jan 06          dog
> > > > > Jan 07          cat
> > > > > Jan 08          dog
> > > > > Jan 09          bird
> > > > > Jan 10          cat
> > > > > =============
> > > > > Max            dog
> > > > >
> > > > > "dog" should be retrieved because its frequency was 5 while "cat"
> > > occurred
> > > > 4
> > > > > times and "bird" only once.
> > > > >
> > > > > Thanks,
> > > > > Ron
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
terre08 (1112)
1/15/2004 9:43:21 PM
 Peo Sjoblom and Frank

Thanks for all the help on this problem - everything is working correctly
for me now.

Ron



"Peo Sjoblom" <terre08@mvp.org> wrote in message
news:eMIxcC72DHA.1184@TK2MSFTNGP10.phx.gbl...
> Frank's formula works if the first cell is empty.. Regardless the reason
the
> error with one value only is the mode function,
> try to put single number in a range and use mode
>
> =MODE(A1:A10)
>
> mode needs at least 2 values, so you have to condition that before the
mode
> part, also note that mode will return an error
> if all values are unique so if you have 2 entries and they are "x" and "y"
> mode will retrain an error even if you would dodge the single entry with
> something like
>
>
=IF(COUNTA(A1:A10)=0,"",IF(COUNTA(A1:A10)=1,INDEX(A1:A10,MATCH(TRUE,A1:A10<>
> "",0)),INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))))))
>
> However I would use this
>
>
=IF(COUNTA(A1:A10)=0,"",INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNT
> IF(A1:A10,A1:A10),0),1))
>
> array entered
>
> -- 
>
> Regards,
>
> Peo Sjoblom
>
>
> "news.newsguy.com" <idontknow@questioning> wrote in message
> news:bu6tip02uan@enews1.newsguy.com...
> > Hopefully I'm not being a pest but here's another question concerning
this
> > formula and a little more background info.
> >
> > The spreadsheet for which I need this formula is used in the following
> > manner.
> > Column A is the day/date of the month and the other columns are used for
> > flows or other operational data.  I have a couple of columns in which
the
> > user will enter the name of a chemical added to a process. Under the row
> for
> > the last day of the month is the AVERAGE row.  I can average the numeric
> > entries with no problem.  I wish to retrieve the chemical name used most
> > frequently during the month in this AVERAGE cell for that column.
> >
> > It may be that no chemicals were used and the Average cell should be
> blank.
> > Multiple chemicals could be used and the most frequently used (or in the
> > case of a tie the first chemical listed) would be the value in the
Average
> > cell.  Any given day could have either a chemical name entered or be
> blank.
> >
> > I can modify your formula to cover the instance where no entries are
made
> so
> > that the AVERAGE cell is empty.
> >
> >
>
=IF(COUNTA(A1:A10)=0,"",INDEX(A1:A10,MODE(IF(A1:A10<>"",MATCH(A1:A10,A1:A10,
> > 0)))))
> > But if there is only 1 entry for the month then the formula returns the
> #N/A
> > error.  If there are at least 2 entries then it works.
> > Is there anyway to find and return the value for a single entry in a
> column
> > when the other cells are empty?
> >
> >
> > Frank's array formula works for either all empty or a single entry but
if
> > the first cell (B1) is empty then it doesn't return anything regardless
of
> > how many other entries were made.  Is there anyway to fix that?
> >
>
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0),1)
> >
> > I do appreciate the help from both of you.
> > Ron
> >
> >
> > "Peo Sjoblom" <terre08@mvp.org> wrote in message
> > news:upZTJY42DHA.556@TK2MSFTNGP11.phx.gbl...
> > > You could add this to the formula
> > >
> > > =INDEX(B1:B10,MODE(IF(B1:B10<>"",MATCH(B1:B10,B1:B10,0))))
> > >
> > > although now it has to be entered with ctrl + shift & enter, or you
> could
> > > use
> > > Frank's array formula
> > >
> > >
> >
>
=INDEX(B1:B10,MATCH(MAX(COUNTIF(B1:B10,B1:B10)),COUNTIF(B1:B10,B1:B10),0),1)
> > >
> > > -- 
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > >
> > > "news.newsguy.com" <idontknow@questioning> wrote in message
> > > news:bu6atm01nrc@enews1.newsguy.com...
> > > > Thanks for the response that helped a lot.
> > > > What suggestion would you have for a change to the formula if there
> was
> > no
> > > > entry for a given day.
> > > >
> > > > Ex:
> > > > Jan 01          cat
> > > > Jan 02          dog
> > > > Jan 03          cat
> > > > Jan 04          dog
> > > > Jan 05          dog
> > > > Jan 06          dog
> > > > Jan 07
> > > > Jan 08          dog
> > > > Jan 09          bird
> > > > Jan 10          cat
> > > >  =============
> > > > Max            dog
> > > >
> > > > The current formula returns a #N/A error.
> > > >
> > > > Thanks,
> > > > Ron
> > > >
> > > >
> > > > "Peo Sjoblom" <terre08@mvp.org> wrote in message
> > > > news:OqWhRFs2DHA.2604@TK2MSFTNGP09.phx.gbl...
> > > > > One way
> > > > >
> > > > > =INDEX(B1:B10,MODE(MATCH(B1:B10,B1:B10,0)))
> > > > >
> > > > >
> > > > > where B1:B10 holds your animals
> > > > >
> > > > > -- 
> > > > >
> > > > > Regards,
> > > > >
> > > > > Peo Sjoblom
> > > > >
> > > > >
> > > > > "news.newsguy.com" <idontknow@questioning> wrote in message
> > > > > news:bu3qrv0ie7@enews1.newsguy.com...
> > > > > > I have an Excel 2000 spreadsheet used for daily entries for any
> > given
> > > > > month.
> > > > > > Some of the columns have text values instead of numeric.  It is
> > > possible
> > > > > > that the text entries would be the same for every day or they
> could
> > > vary
> > > > > > between 2 or 3 different text values.  I would like to know if
> there
> > > is
> > > > a
> > > > > > formula that would retrieve the most frequently entered TEXT.
> > > > > >
> > > > > > Ex:
> > > > > >
> > > > > > Jan 01          cat
> > > > > > Jan 02          dog
> > > > > > Jan 03          cat
> > > > > > Jan 04          dog
> > > > > > Jan 05          dog
> > > > > > Jan 06          dog
> > > > > > Jan 07          cat
> > > > > > Jan 08          dog
> > > > > > Jan 09          bird
> > > > > > Jan 10          cat
> > > > > > =============
> > > > > > Max            dog
> > > > > >
> > > > > > "dog" should be retrieved because its frequency was 5 while
"cat"
> > > > occurred
> > > > > 4
> > > > > > times and "bird" only once.
> > > > > >
> > > > > > Thanks,
> > > > > > Ron
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
news
1/22/2004 2:19:04 PM
Reply:

Similar Artilces:

TempVars unusable in field default value
Hello, I'm trying to use a temporary variable to keep track of which CSR is inputting data. I have a macro which prompts user for ID code, which is stored in the temp variable TempUser. On a form control default value property, I can use the expression [TempVars]![TempUser], which will populate that user's ID code into the control. However, I cannot use that same expression in the tables field default value property. If I try, when I save the changes to the table, I get the error message "Could not find the field 'TempVars]![TempUser'. " Any ideas why I ca...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

rounding up values
Has anyone done round up of values to the nearest dollar.For example I want to give a 10% of the price to my customers but if the result is other than .00 then I wanted to round up to the nearest dollar amount.My calculation using sql has been price * percent and then subtract the value from the price, then what do I need to do to roundit up??Thanks for your suggestion.Also I have a problem with my customers that I am extracting and the query does return all the values from 2004 and 2006 that are equal except for the price I have given them, how do I get only the latest ones in 2006 and not th...

100% stacked column chart shows wrong information
I wonder if someone can help me. I have created a table that tracks the profitability of a particular project and I’m using the table as a source for a 100% Stacked Column graph. The source data for the graph is in three rows: • B21:M21 - contain the names of the months from Nov – Oct • B22:M22 - money taken in • B24:M24 - profit B23:M23 contains the money paid to supplier each month. I need this in the table but I don’t need to show it in the graph. I just want to show profit as a proportion of what we bill to customers on that project. My problem is that the graph seems to show in...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

pulling certain characters from a string of text
I need to look up "certain critera" within a string of characters, then return that "certain criterea" to a new column. Some examples of a strings of characters may look like these: K5J091509001 Sample PO#S881009 K55sample PO CarrieRJR TJ5 My "Certain Critera" I have listed on another sheet, named "REP ID" K5J S88 K55 RJR TJ5 How do I pull out the 3 characters of "Certain Criterea" from the string of text and copy or enter it into a new column? Hi, =left(a1,3) "SaraMack" wrote: > I need to look up "c...

can I snap wrap points to a text box
rather than having to add individual wrap points to the edge of a frame, which is never as accurate anyway, can they be set to 'snap' to a frame (eg the ellipse) so that they are perfectly inline, (and which would of course be a lot quicker)? Edit points will not snap. There are options for edit points, select a point, right click. If you hold down control, the cursor will turn into an x, you can delete a point with a click. Truly a good draw program would be preferable. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com &q...

Sorting on Column headers
I have a spreedsheet that someone else created and I need to take the Column headers, which are in date form, and rearrange them in chronological order. Can this be done without cutting and pasting? I really need to save time with this project if possible. Thanks!:) --- Message posted from http://www.ExcelForum.com/ > I have a spreedsheet that someone else created and I need to take the > Column headers, which are in date form, and rearrange them in > chronological order. Can this be done without cutting and pasting? I > really need to save time with this project if possib...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

line chart with NA() values
12 month line chart, with some values being 0. I am using an if statement that turns any 0 values to #N/A so they do not show on the graph (which is what I want). My problem arises when the 0 values fall in the middle of my data. So for example: 1) data for all months (Jan-Dec), the line shows across all 12 months; 2) I have data for only 6 months (Jul-Dec), the line starts in Jul and ends in Dec (perfect); 3) When I have data from Jan-Mar, and Oct-Dec, the line connects between Mar and Oct. I want 2 distinct lines with no line where there is no data (#N/A). Any suggestions? -- gri...

Multivalue with Null value SSRS 2005
I have a query to populate a multivalue parameter: SELECT distinct cast(AGRPYear.value as varchar(4)) + AGRPMonth.value 'ReportDate' FROM TPROJECT AS TPROJECT One of the values that is returned from this query is NULL. However, when I run the report, the NULL value does not show in the dropdown. I've also tried adding "select NULL as 'ReportDate' union" to the above query and the null value still doesn't show. As a result some of the records in my database have a null value for this field, they will never show up on my report. Any id...

default text height comment
Is there a way to set the default text height for a new comment? Thanks mark (I've looked through help but can't find it if it's in there.) I assume you mean the font size? There is no text height available in Excel. A comment has a shape property and that is what you can use to change the font size. They didn't make it easy ... Range("D4").Comment.Shape.TextFrame.Characters.Font.Size = 12 -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (free and commercial excel programs) "mp" <nospam@Thanks.com> wrote in message...

Change the text of a shape rather than its master
Hi, I build custom masters by mixing two general shapes, say square and circle together, and have text on both the shapes. But after I drop an instance of the master into a page, I cannot modify the text of the instance. To do so, I need to modify the text on the master, which is non-sense for me. How to change the text of a shape without modifying its master? Thanks! How are you doing this? By code or by the UI? Are you grouping the shapes? If you drag two shapes to the stencil, it will group the shapes. So instead of a square and a circle you have three shapes. A Square, Circle and the...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

Customize columns in 'Marketing List Members'
I can't figure out where one can customize the columns used within the "marketing list" entitry when you click the 'marketing list members' on the left side to show the members. I want to add some columns, like Email. Screenshot: http://i355.photobucket.com/albums/r469/canadaka_bucket/marketing_list_members.jpg Just read the Posting on the Microsoft CRM Team Blog. <canadaka@gmail.com> schrieb im Newsbeitrag news:306584c6-2043-4962-b12a-d0b9287684bb@b31g2000prb.googlegroups.com... > I can't figure out where one can customize the columns used within the >...

Removing spaces from text #4
I'm in excel and i have a bunch of text data that has an extra space at the end of the text on the right hand side for each cell. Is there any easy way to remove this space? Use the TRIM() function. -- Kind regards, Niek Otten "lj" <lj@spu.edu> wrote in message news:1144876429.220961.309040@j33g2000cwa.googlegroups.com... > I'm in excel and i have a bunch of text data that has an extra space at > the end of the text on the right hand side for each cell. Is there any > easy way to remove this space? > I tried using that function but the results st...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Character Length in a column
I have a column where every cell must have 14 characters. Lets say have the name Angel. I need 9 more charcters. Next cell down have the word cake. I need 10 more charcters.How do I automatciall insert the spaces I need to have 14 charcters. HELP PLEASE!!!!! GOIN NUTS ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements One way: Do you care about what characters are added? I'l...

Freeze the side column/top row & scroll others
what is the function to set (lock in or freeze) the first column and / or top row of a spreadsheet, so the words and numbers remain in the same place as you scroll the other columns and rows. (so you can add more columns..yet keep the main information in the first column/row) Freeze Panes..... In older versions of Excel, it is under Window. In 2007 version of Excel, it is under View. You first select a cell, then activate the command. Excel uses the selected cell's upper left corner to define the freeze point. Play with it. You can also Unfreeze panes that were fro...