String Searching

[arapone]

Alright, I am not the most proficient excel user, but do have some decent knowledge of the program and functions. I am having difficulty with one particular search. I want to look up the greatest value in a row and display that value in a particular cell. The row can contain any letter from A-Z and as well - when a value is non-applicable. How can i do a search to display the highest value? I have tried using if statements going greater to and so forth, but its getting quite ridiculous when in certain applications, there are 50+ rows to search.

 

[Oaktree]

...or, you could wrap something like

=IF(CHAR(LARGE(CODE(AJ6:AO6),1))="-","","The latest drawing revision ("&CHAR(LARGE(CODE(AJ6:AO6),1))&") was submitted on "&TEXT(INDEX(AJ$5:AO$5,MATCH(CHAR(LARGE(CODE(AJ6:AO6),1)),AJ6:AO6,0)),"mm/dd/yy"))

to show no message if the row has only -'s

 

[arapone]

awsome!!

I had modified it to remove some of the text to fit it in a cell

="("&CHAR(LARGE(CODE(AH8:AM8),1))&") "&TEXT(INDEX(AH$5:AM$5,MATCH(CHAR(LARGE(CODE(AH8:AM8),1)),AH8:AM8,0)),"mm/dd/yy")

what about if it returns - to display in the cell (-) Not Yet Submitted instead of (-) 06/08/22 (which is displayed)

 

[Oaktree]

(see my previous post--I posted another formula to deal with that)

 

[arapone]

I pluged it in and it still returns (-) and then the latest date which was entered.

 

[Oaktree]

With this formula?

=IF(CHAR(LARGE(CODE(AJ6:AO6),1))="-","","The latest drawing revision ("&CHAR(LARGE(CODE(AJ6:AO6),1))&") was submitted on "&TEXT(INDEX(AJ$5:AO$5,MATCH(CHAR(LARGE(CODE(AJ6:AO6),1)),AJ6:AO6,0)),"mm/dd/yy"))

 

[arapone]

sorry.. i mean it just pulls TRUE

 

[Oaktree]

Hmm...

AI6 =

{=IF(CHAR(LARGE(CODE(AJ6:AO6),1))="-","Not Yet Submitted","The latest drawing revision ("&CHAR(LARGE(CODE(AJ6:AO6),1))&") was submitted on "&TEXT(INDEX(AJ$5:AO$5,MATCH(CHAR(LARGE(CODE(AJ6:AO6),1)),AJ6:AO6,0)),"mm/dd/yy"))}

copied down

Book20
	AI	AJ	AK	AL	AM	AN	AO
5		9/18/2006	9/17/2006	9/16/2006	9/15/2006	9/14/2006	9/13/2006
6	Not Yet Submitted	-	-	-	-	-	-
7	The latest drawing revision (D) was submitted on 09/18/06	D	-	-	-	B	-
Sheet1

 

[arapone]

It pulls up #Value ! when there is a letter there, but works when there is just a -

 

[arapone]

http://ca.pg.photos.yahoo.com/ph/fouselli/detail?.dir=/432dre2&.dnm=ef9cre2.jpg&.src=ph

its pulling wierd errors on the top most enteries, but the rest work without any issue. Any idea why this could be?

i used the forumua as follows:

=IF(CHAR(LARGE(CODE(AH20:AM20),1))="-","Not Yet Submitted","("&CHAR(LARGE(CODE(AH11:AM11),1))&") "&TEXT(INDEX(AH$5:AM$5,MATCH(CHAR(LARGE(CODE(AH20:AM20),1)),AH20:AM20,0)),"mm/dd/yy"))

 

[Oaktree]

You shouldn't have CODE(AH11:AM11) in the middle. It should be CODE(AH20:AM20) to be consistent with the other ranges.

The 20's or 11's or whatever should match the row that you're putting the formula into. (e.g. if you're entering to AI6, they should be 6's. If you're entering to AI29, they should be 29's)