Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default return text value found most frequently in a column

I have a column of text with 367 entries, author's names. I have used
SUMPRODUCT to determine that there are 267 unique entries. Is there a
formula I can now use to give me the author's name that appears most
frequently? Thanks for your help.

globetrotter
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default return text value found most frequently in a column

Try this array formula** :

=INDEX(A1:A367,MODE(IF(A1:A367<"",MATCH(A1:A367,A 1:A367,0))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"globetrotter" wrote in message
...
I have a column of text with 367 entries, author's names. I have used
SUMPRODUCT to determine that there are 267 unique entries. Is there a
formula I can now use to give me the author's name that appears most
frequently? Thanks for your help.

globetrotter



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default return text value found most frequently in a column

Another option, array-entered* in say, B2:
=INDEX(A2:A400,MATCH(MAX(COUNTIF(A2:A400,A2:A400)) ,COUNTIF(A2:A400,A2:A400),0))

*Press CTRL+SHIFT+ENTER to confirm the formula

In the event of ties in the max counts, then only the 1st "max" name (the
one higher up) will be returned
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"globetrotter" wrote:
I have a column of text with 367 entries, author's names. I have used
SUMPRODUCT to determine that there are 267 unique entries. Is there a
formula I can now use to give me the author's name that appears most
frequently? Thanks for your help.

globetrotter

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default return text value found most frequently in a column

Try this *none array entered*

=INDEX(A1:A367,MODE(INDEX(MATCH(A1:A367,A1:A367,), )))


"globetrotter" wrote:

I have a column of text with 367 entries, author's names. I have used
SUMPRODUCT to determine that there are 267 unique entries. Is there a
formula I can now use to give me the author's name that appears most
frequently? Thanks for your help.

globetrotter

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default return text value found most frequently in a column

Thanks for the helpful and quick responses. I got just what I was looking for.

globetrotter

"Teethless mama" wrote:

Try this *none array entered*

=INDEX(A1:A367,MODE(INDEX(MATCH(A1:A367,A1:A367,), )))


"globetrotter" wrote:

I have a column of text with 367 entries, author's names. I have used
SUMPRODUCT to determine that there are 267 unique entries. Is there a
formula I can now use to give me the author's name that appears most
frequently? Thanks for your help.

globetrotter



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default return text value found most frequently in a column

can u explain how formula works. I know all of these formulas. Yes it it
works, but i cannot understand 'em.

"T. Valko" wrote:

Try this array formula** :

=INDEX(A1:A367,MODE(IF(A1:A367<"",MATCH(A1:A367,A 1:A367,0))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"globetrotter" wrote in message
...
I have a column of text with 367 entries, author's names. I have used
SUMPRODUCT to determine that there are 267 unique entries. Is there a
formula I can now use to give me the author's name that appears most
frequently? Thanks for your help.

globetrotter




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default return text value found most frequently in a column

MATCH(A1:A367,A1:A367,0) returns an array of the first row number of each
separate value, so you will get many repetitions per value

As there may be blanks, which return #N/A, this is catered for by

IF(A1:A367<"",MATCH(A1:A367,A1:A367,0))

so as to get an array with row numbers and FALSE (many functions nicely
ignore FALSE and don't error as they would with #N/A

MODE(IF(A1:A367<"",MATCH(A1:A367,A1:A367,0)))

then returns the most frequently occurring, or repetitive, value in that
array

and it is passed to INDEX to get the actual value.

QED


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Vusal" wrote in message
...
can u explain how formula works. I know all of these formulas. Yes it it
works, but i cannot understand 'em.

"T. Valko" wrote:

Try this array formula** :

=INDEX(A1:A367,MODE(IF(A1:A367<"",MATCH(A1:A367,A 1:A367,0))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"globetrotter" wrote in message
...
I have a column of text with 367 entries, author's names. I have used
SUMPRODUCT to determine that there are 267 unique entries. Is there a
formula I can now use to give me the author's name that appears most
frequently? Thanks for your help.

globetrotter






  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default return text value found most frequently in a column


Build up the formula from its elements to see intermediate results and
understand its workings


+-------------------------------------------------------------------+
|Filename: ArrayFormula.xlsx |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=72|
+-------------------------------------------------------------------+

--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60302

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
Return column number from column header text Roger[_3_] Excel Discussion (Misc queries) 4 February 14th 08 10:40 PM
Return Frequently occuring Rows, based on COUNT Brooks Excel Worksheet Functions 4 April 27th 07 01:41 AM
Vlookup, return zero if not found molsansk Excel Worksheet Functions 2 August 22nd 06 06:40 PM
Extracting the most frequently occuring text from a range Phil Excel Worksheet Functions 5 June 9th 06 01:39 PM
Return text found in a search Dave R. Excel Worksheet Functions 4 May 12th 05 08:53 PM


All times are GMT +1. The time now is 03:44 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"