Show "highest" letter

**Stuand** · 09-07-2011, 03:44 AM

Hi all,

I have a spreadsheet that records the last issue number of a document.

ie a column of numbers that goes 1,2,3,4...etc. I then use the formula "=max(A1:A6000) that simply gives me the latest, (ie largest) number that I can then feed into various sub worksheets.

However, some people use text letters for issue control.....ie a.b,c,d...etc.

Is there any way to perform a simple calculation that will look at all the letters in a column and report the "last" one used?

Thanks,

Stu

**Andrew-R** · 09-07-2011, 04:48 AM

I think the easiest way to go would be to put in a 'helper' column

If your list of number is in column A starting at row 1 then insert a new column B and in row 1 put =IF(ISNUMBER(A1),0,CODE(A1)) and copy all of the way down.

Now =MAX(A1:A6000) will continue to give you the highest number used, but =CHAR(MAX(B1:B6000)) will give you the highest letter used.

Note that upper and lower case letters will be treated differently, so 'a' will be considered a higher value than 'Z' - to get round this modify the formula in column B to =IF(ISNUMBER(A1),0,CODE(LOWER(A1)))

**Stuand** · 09-07-2011, 05:14 AM

Hi Andrew..

many thanks for your response...I was hoping for something a little simpler, but at least this gives me a fix...

again many thanks for your time...

Stu

**PaulG2011** · 09-07-2011, 05:18 AM

Hi stuand,

The only other way I would know how to do it is to use a "user defined function" (i.e. a macro) that you can call like a formula. So your formula would be something like:

=gethighestrev(A1:A10)

and then you would need a function called "gethighestrev" in a module on the visual basic editor with code to scan through the range and find the highest "value".

Good luck!

Paul

**Andrew-R** · 09-07-2011, 05:22 AM

Yup, a UDF would do it and would be fairly easy to write, but might be slow if there really are 6,000 rows of data.

**PaulG2011** · 09-07-2011, 05:24 AM

Ah yes - didn't spot that! I guess if he just needs to run it once then copy+paste over the "highest" row with values he might be alright? If its something that hes going to have to do everytime he opens the workbook or something it might be slow whatever he does with that much data.

**daddylonglegs** · 09-07-2011, 05:26 AM

You could achieve the same as Andrew's first suggestion, using an "array formula" rather than a helper column - e.g. this formula

=CHAR(MAX(IF(ISERR(A1:A6000+0),CODE(A1:A6000))))

confirmed with CTRL+SHIFT+ENTER

**Stuand** · 09-07-2011, 05:28 AM

whooooaaaaa...

way out of my league.....I only put in "6000", a a "force of habit" to capture everything...in truth issue levels would not run much past 15 or 20 odd levels.....

...many thanks for your time anyway.....

Stu

**Stuand** · 09-07-2011, 05:32 AM

Hi all,

have since found this "=CHAR(MAX(INDEX(CODE(A1:A6000&CHAR(1)),0)))...which seems to work nK...but I don't know how!!

(NB Excel 2003...could not get "Daddylonglegs" formula above to work...but again thanks for the imput)

Stu

Stu

**daddylonglegs** · 09-07-2011, 05:45 AM

Hello Stu,

My suggestion requires CTRL+SHIFT+ENTER

to do that you put the formula in a cell - press F2 to select the formula then hold down CTRL and SHIFT keys and press ENTER. If done correctly then curly braces like { and } appear around the formula in the formula bar.

The version you quoted is very similar but the INDEX function is used simply to avoid needing CTRL+SHIFT+ENTER

It works without INDEX like this

=CHAR(MAX(CODE(A1:A6000&CHAR(1)),0))

.....but that needs CSE

Note that as Andrew said the formula picks out the "highest" lower case letter so if you have s and Z in the range s will rank higher than Z.......