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

 

[Michael M]

Based on your data, you can simply use the MAX function

=MAX(A2:AQ2)

 

[Teeroy]

Try =lookup(max($b$2:$l$2),$b$2:$l$2,$a$2:$k$2)

 

[JBijl]

Hi Michael,

Thanks for your reply. Yes - your formula gives me the highest count in the row BUT what I really need is for the cell AR2 to give me the text that corresponds to the highest value in characters. So if cell AG2 shows that AF2 has 280 characters, then cell AR2 should show the text that is in AF2.

Hope that clarifies things.

Thank you for your help!!!

 

[JBijl]

Try =lookup(max($b$2:$l$2),$b$2:$l$2,$a$2:$k$2)

Thanks but not sure how to use this

 

[JBijl]

Based on your data, you can simply use the MAX function

=MAX(A2:AQ2)

I also see that I have not put text in the example sheet for the other cells. Please find an adjusted file here: https://www.dropbox.com/s/cc0y29yiqb76ray/Project 77 EN 2.xls

 

[Teeroy]

Thanks but not sure how to use this

Copy and paste the formula into the cell where you want the longest text retrieved.
The sample I used goes up to column "L" to extend to "AR" use:

=lookup(max($b$2:$ar$2),$b$2:$ar$2,$a$2:$aq$2)

(just hit enter, its not an array formula)

 

[JBijl]

Copy and paste the formula into the cell where you want the longest text retrieved.
The sample I used goes up to column "L" to extend to "AR" use:

=lookup(max($b$2:$ar$2),$b$2:$ar$2,$a$2:$aq$2)

(just hit enter, its not an array formula)

Hi Teeroy,

Thanks for your help so far. I have tried, tested and played around with your formula but cant seem to get the correct outcome. It gives me the text of one of the cells in the AR-cell but it is not the highest count. And when I copy the formula down (have to remove the $-signs before the cell numbers) it seems to get confused.

What am I doing wrong?

Thanks again.

 

[Teeroy]

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?

 

[Jonmo1]

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))