Complicated comparison between cells using LEN and....?

[JBijl]

Hi guys and ladies,

I have yet another question that I hope somebody here knows the answer to. I have the following situation:

A single Excel sheet with a total of 44 columns that contain content. The content is devided as follows:
A2 has text => B2 has LEN function to count the characters.
C2 has text => D2 has LEN function to count the characters.
E2 has text => F2 has LEN function to count the characters.
G2 has text => H2 has LEN function to count the characters.
I2 has text => J2 has LEN function to count the characters.
K2 has text => L2 has LEN function to count the characters.
etc...

In the last column I need to get the text that has the most characters from the prior cells. So if cell A2 has 120 characters, C2 has 125, E2 has 128, G2 has 103 etc... then in the last cell (AR2) would show the content of E2.

I know how to get the character counts per cell but is there a way to automate doing the compare between the different LEN results and then put the text from the cell with the highest result as actual text in the last cell?

Here is the link to the file https://www.dropbox.com/s/2jp5ie77vc4pdsq/Project 77 EN.xls. I hope you can work with it.

Thanks so much in advance for any help you all might be able to give.

Cheers
Jacob

 

[JBijl]

It might be easier if I could look at the workbook. Can you put a copy of your workbook on a fileshare site (e.g. dropox) and post a hyperlink to it?

Hey Teeroy,

Thank you for your persistance. I had already posted the file link to my dropbox file. However, Jonmo1 has sent me a working formula. Thanks for your help with it.

Cheers Jacob

 

[JBijl]

Lookup won't work here, because it requires the data to be sorted Ascending, which it clearly is not..

Try

=INDEX($a$2:$aq$2,MATCH(MAX($b$2:$ar$2),$b$2:$ar$2,0))

You rock! That is the ticket!

Thanks to all who helped...

Cheers
Jacob

 

[Teeroy]

Jacob, Johnmo1's formula is the trick. I had (unintentionally) set up data that was increasing in text length, therfore the numerical data was sorted ascending.

 

[JBijl]

Jacob, Johnmo1's formula is the trick. I had (unintentionally) set up data that was increasing in text length, therfore the numerical data was sorted ascending.

Thanks again Teeroy. You came a hell of a lot closer to the solution than me... I am just really glad that there are people like you and Jonmo1 to help out.

Cheers Jacob