Most common string in column with an exception

**Bytor47** · 04-10-2013, 01:53 PM

I am trying to find the most common string in a column but I need to ignore a certain value.

I found this formula in another thread and it works great for it's intended purpose but I need to modify it to ignore 0's.

=INDEX(A2:A10,MODE(MATCH(A2:A10,A2:A10,0)))

Example Data:

john
mary
john
0
0
mary
bill
john
0
0

The result should be john not 0. Is this possible?

Any help is greatly appreciate.

Thanks
Rob

**JieJenn** · 04-10-2013, 02:12 PM

Not smart enough to come up with a formula solution, so try UDF

Please Login or Register  to view this content.

**Bytor47** · 04-10-2013, 02:39 PM

Thanks for the quick response. I'll give that a try but in the meantime would it help if I changed the 0's to blanks?

Data:

john
mary
john
(empty cell)
(empty cell)
mary
bill
john
(empty cell)
(empty cell)
bill

Result = john

**JieJenn** · 04-10-2013, 02:44 PM

If you replace 0 with empty cell then you can use an old school array formula
Enter with Ctrl + Shift + Enter keys together

=INDEX(A1:A8,MATCH(MAX(COUNTIF(A1:A8,A1:A8)),COUNTIF(A1:A8,A1:A8)))

**daddylonglegs** · 04-10-2013, 02:48 PM

You could keep the zeroes and use this version

=INDEX(A2:A10,MODE(IF(A2:A10<>0,MATCH(A2:A10,A2:A10,0))))

confirmed with CTRL+SHIFT+ENTER

There needs to be at least 2 of one of the names otherwise you get #N/A

**Bytor47** · 04-11-2013, 07:46 AM

Thanks for all the help from both of you.

I'll play around with each of them and figure out which is most appropriate for what I'm doing.

Thanks again.