names in a long list

F

ferde

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.
 
P

Peo Sjoblom

=INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0)))

or

=INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),COUNTIF(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
 
D

daddylonglegs

Try this formula

=INDEX(A1:A200,MATCH(MAX(COUNTIF(A1:A200,A1:A200)),COUNTIF(A1:A200,A1:A200),0))

confirmed with CTRL+SHIFT+ENTER
 
F

ferde

Thank you ,,,it works great

Peo Sjoblom said:
=INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0)))

or

=INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),COUNTIF(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
Click to expand...
 
Top