Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SORT A LIST OF NUMBERS AND LETTERS A,B,C,D AND FIND MAX
I have a drawing register that lists drawing numbers and the last time it was
issued. Initially the drawing is issued with a numbers, however as the project continues, the number changes to a letter, eg A, then B, C etc. If the drg has been issued as a number I want to extract the largest number, however if its a letter, then i want to find the latest letter eg a particular drg may be issued S1001 1 2 3 4 5 A B C so i want to be able to extract C S1002 1 2 3 so i want to be able to extract 3 S1003 A B C D E so i want to be able to extract E its ok if there are only a couple of drawings, but 500+ drgs takes a while to sort out |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SORT A LIST OF NUMBERS AND LETTERS A,B,C,D AND FIND MAX
There may be a simpler solution, but if your data is in B1:I1 and assuming
your numbers could be = 10, try: =IF(SUM(--ISTEXT(B1:I1)),CHAR(MAX(IF(B1:I1<"",CODE(B1:I1)," "))),MAX(B1:I1)) If your numbers will always be < 10, try shortening it to: =CHAR(MAX(IF(B1:I1<"",CODE(B1:I1),"")) Both are array entered (entered with Control+Shift+Enter, not just the enter key) "Darryl" wrote: I have a drawing register that lists drawing numbers and the last time it was issued. Initially the drawing is issued with a numbers, however as the project continues, the number changes to a letter, eg A, then B, C etc. If the drg has been issued as a number I want to extract the largest number, however if its a letter, then i want to find the latest letter eg a particular drg may be issued S1001 1 2 3 4 5 A B C so i want to be able to extract C S1002 1 2 3 so i want to be able to extract 3 S1003 A B C D E so i want to be able to extract E its ok if there are only a couple of drawings, but 500+ drgs takes a while to sort out |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SORT A LIST OF NUMBERS AND LETTERS A,B,C,D AND FIND MAX
It looks like you'd like to return the last value in the row. If so,
try... =LOOKUP(2,1/(Range<""),Range) Hope this helps! In article , Darryl wrote: I have a drawing register that lists drawing numbers and the last time it was issued. Initially the drawing is issued with a numbers, however as the project continues, the number changes to a letter, eg A, then B, C etc. If the drg has been issued as a number I want to extract the largest number, however if its a letter, then i want to find the latest letter eg a particular drg may be issued S1001 1 2 3 4 5 A B C so i want to be able to extract C S1002 1 2 3 so i want to be able to extract 3 S1003 A B C D E so i want to be able to extract E its ok if there are only a couple of drawings, but 500+ drgs takes a while to sort out |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding most common occurence of values in cells containing letters and numbers | Excel Worksheet Functions | |||
Change the header columes back to letters not numbers | New Users to Excel | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
VLOOKUP for a cell with both letters and numbers | Excel Discussion (Misc queries) | |||
Finding Numbers with Cells that also contain letters | Excel Discussion (Misc queries) |