Excel 2007 : make "text" like "mode" fuction

hello again excel experts, i need a way to convert text into a mode function, i looked around everywhere to find a formula to make this before i made this post, bu could not find anything on this manor on this formula. i got a file for 2007 so take a look,

if anyone can help this will be most appreciated, if not i'll do my best to find out how on my own XD.

W5 formula is fine since it's the first find.

Put this in W6 and copy down:

=IF(Y6<>Y5, VLOOKUP(Y6, $Q$5:$R$30, 2, 0), INDEX(INDEX(R:R, MATCH(W5, R:R, 0)+1):INDEX(R:R, MATCH("ZZ", R:R, 1)), MATCH(Y6, INDEX(Q:Q, MATCH(W5, R:R, 0)+1):INDEX(Q:Q, MATCH("ZZ", R:R, 1)), 0)))

If you wanted one formula for W5:W8 allowing for Merged Cells then:

Please Login or Register  to view this content.

If for whatever reason you didn't want to use the Alphabet lookup table you could (if you removed the merge from cells W5:W8) use:

Please Login or Register  to view this content.

though the ordering of the duplicates would differ slightly from the first formula (and JBs) - unclear if this is of concern or not.