Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. Write pls "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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return column number from column header text | Excel Discussion (Misc queries) | |||
Return Frequently occuring Rows, based on COUNT | Excel Worksheet Functions | |||
Vlookup, return zero if not found | Excel Worksheet Functions | |||
Extracting the most frequently occuring text from a range | Excel Worksheet Functions | |||
Return text found in a search | Excel Worksheet Functions |