Thread: Finding the maximum occurrence of text over non-consecutive cells

Hi All

I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other

Some of the cells will also be blank

And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like

Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments!

I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula

Code:

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

works ok other than when there is a draw

Any help would be most appreciated as this is starting to drive me up the wall!

Many thanks in advance

Rae

Rae, I will have a think about DRAW but in the meantime this should give you the MODE:

Code:

BF2:
=INDEX($O2:$AY2,MODE(IF((LEFT($O$1:$AY$1,4)="Note")*($O2:$AY2<>""),MATCH($O2:$AY2,$O2:$AY2,0))))
committed with CTRL + SHIFT + ENTER
copied down

Rae, I can't think of a way to do the DRAW test in one formula - someone else may well be able to... but a workaround would be to return the 2 most frequent strings and then analyse the instances of each to determine if unique or not, eg:

Code:

BR2:
=INDEX($O2:$AY2,MODE(IF((LEFT($O$1:$AY$1,4)="Note")*($O2:$AY2<>""),MATCH($O2:$AY2,$O2:$AY2,0))))
committed with CTRL + SHIFT + ENTER
copy down

BS2:
=INDEX($O2:$AY2,MODE(IF((LEFT($O$1:$AY$1,4)="Note")*($O2:$AY2<>"")*($O2:$AY2<>$BR2),MATCH($O2:$AY2,$O2:$AY2,0))))
committed with CTRL + SHIFT + ENTER
copy down

BT2:
=IF(ISERROR($BS2),-1,SUMPRODUCT(--(LEFT($O$1:$AY$1,4)="Note"),($O2:$AY2=$BR2)-($O2:$AY2=$BS2)))
copy down

You can then use BT etc to determine final output in BF such that:

Code:

BF2:
=IF(BT2,BR2,"DRAW")
copy down