| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
names in a long list
a1:a200 are a list of names. I need to determine which name is appearing
most often AND have the name populate B2 in the same worksheet. Would appreciate any help you can offer. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
names in a long list
=INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0)))
or =INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),CO UNTIF(A1:A20,A1:A20),0)) both entered with ctrl + shift & enter, the first one is shorter but returns error if there are blank cells within the range adapt to fit your range -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "ferde" wrote in message ... a1:a200 are a list of names. I need to determine which name is appearing most often AND have the name populate B2 in the same worksheet. Would appreciate any help you can offer. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
names in a long list
Thank you ,,,it works great
"Peo Sjoblom" wrote: =INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0))) or =INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),CO UNTIF(A1:A20,A1:A20),0)) both entered with ctrl + shift & enter, the first one is shorter but returns error if there are blank cells within the range adapt to fit your range -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "ferde" wrote in message ... a1:a200 are a list of names. I need to determine which name is appearing most often AND have the name populate B2 in the same worksheet. Would appreciate any help you can offer. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
names in a long list
Try this formula =INDEX(A1:A200,MATCH(MAX(COUNTIF(A1:A200,A1:A200)) ,COUNTIF(A1:A200,A1:A200),0)) confirmed with CTRL+SHIFT+ENTER -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521577 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
names in a long list
Thank you for your reply
"daddylonglegs" wrote: Try this formula =INDEX(A1:A200,MATCH(MAX(COUNTIF(A1:A200,A1:A200)) ,COUNTIF(A1:A200,A1:A200),0)) confirmed with CTRL+SHIFT+ENTER -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521577 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Format cells with a formula (7 conditions). | Excel Discussion (Misc queries) | |||
| Returning one text value in a long list? | Excel Worksheet Functions | |||
| find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions | |||
| Date & Time | New Users to Excel | |||
| Setting up a random list from long list of names ? | Excel Discussion (Misc queries) | |||
Hybrid Mode
