Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning the most duplicated item
Let's say I have a column with a bunch of names in it, and I want a function
that will return the name that shows up the most often (the mode I guess). How do I do that? I know how to do it if the items are numerical data, but how do I do it with text? For example, suppose this is a column: Smith Johnson Smith Roberts Johnson Mulligan Roberts Smith Williams Williams Smith I want a function that will return "Smith" as the name that appears the most often. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning the most duplicated item
Hi Assuming that your data is in A1:A11, then array enter (ctrl, shift, enter) the formula Code: -------------------- =INDEX(A:A,MIN(IF(COUNTIF(A1:A11,A1:A11)=MAX(COUNT IF(A1:A11,A1:A11)),ROW(A1:A11),""))) -------------------- rylo -- rylo ------------------------------------------------------------------------ rylo's Profile: http://www.thecodecage.com/forumz/member.php?userid=28 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=33152 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning the most duplicated item
Assuming no empty cells.
Array entered** : =INDEX(A1:A11,MODE(MATCH(A1:A11,A1:A11,0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Joe Lewis" wrote in message ... Let's say I have a column with a bunch of names in it, and I want a function that will return the name that shows up the most often (the mode I guess). How do I do that? I know how to do it if the items are numerical data, but how do I do it with text? For example, suppose this is a column: Smith Johnson Smith Roberts Johnson Mulligan Roberts Smith Williams Williams Smith I want a function that will return "Smith" as the name that appears the most often. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning the most duplicated item
=INDEX(A1:A11,MODE(INDEX(MATCH(A1:A11,A1:A11,),)))
Just press Enter "Joe Lewis" wrote: Let's say I have a column with a bunch of names in it, and I want a function that will return the name that shows up the most often (the mode I guess). How do I do that? I know how to do it if the items are numerical data, but how do I do it with text? For example, suppose this is a column: Smith Johnson Smith Roberts Johnson Mulligan Roberts Smith Williams Williams Smith I want a function that will return "Smith" as the name that appears the most often. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
GET.CHART.ITEM returning strange results | Charts and Charting in Excel | |||
Item numbers result in item description in next field in Excel | Excel Worksheet Functions | |||
Duplicated cells | Excel Worksheet Functions | |||
Returning the last item in column | New Users to Excel | |||
Selecting an Item from a List and getting a different item to pop. | Excel Worksheet Functions |