Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: Returning MAX value in Column of Version Numbers

    Hi Does anyone know how to do this?

    I have a Column of Values (Application Version Numbers) in Excel 2000

    1.0.1
    1.0.2
    1.0.3
    1.0.4
    1.1.0
    1.1.1
    1.1.2
    1.2.0

    I want to return maximum value (1.2.0) using something like the =MAXA(A1:A10) function (which does'nt work in this case)
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. As a starting step, you could use helper column. Assume your data starts in A2 and goes to A17. Then in B2 put this formula:

    =(MID(A2,LEN(A2)/2+1,1)&RIGHT(A2,1))*1

    Copy it down to then end of your data. Define the named range: MyRng to be A2:B17 (or make it dynamic)

    Then use this formula in C2

    =MAX(B2:B17)

    (or make it a dynamic named range)

    Then this will give your result (in cell D2)

    =INDEX(MyRng,MATCH(C2,$B$2:$B$17,FALSE),1)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Thanks for the help shades

    Nearly got it but not quite - your suggestion did exactly as you said it would but fell over when the version number got to 2.0.1

    My Fault for not including it in the first list -

    Heres a revised list - hope you can help

    1.0.1
    1.0.2
    1.0.3
    1.0.4
    1.1.0
    1.1.1
    1.1.2
    1.2.0
    2.0.6
    2.8.7
    2.9.8
    2.10.9
    2.11.10
    2.11.11
    2.11.12
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Wink bit of the same but still different

    Hi,

    Some what of what shades said.

    But bit different. Had once the same prob but then with dirs
    What you can do is to eliminate the dots,
    and then just use the max value

    hope this help.
    cant give you the code because ive got open office the dutch version
    so the functions are all F.C.U.K.-ed up
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Nice idea marvels but doe'snt work either

    Simply Removing the Dots means 1.11.2 ends up higher than 1.2.0

    Again sorry this scenario is not in the provided version numbers list

    Revised list here

    1.0.1
    1.0.2
    1.0.3
    1.6.4
    1.9.0
    1.11.1
    1.11.2
    1.2.0
    2.0.6
    2.8.7
    2.9.8
    2.10.9
    2.11.10
    2.11.11
    2.11.12
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Re

    Are you going to revise the list every time

    OK hows this 1

    First look at the highest value before the 1st dot then the 2nd and 3rd as last

    ìf first number = 2 and there are no others then that the highest value
    if 2 or more remain with start number 2 then look at 2nd value etc

    See if you can revise your list now
    Last edited by Marvels; 03-13-08 at 09:15.
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    You just need another aspect to handle the change in the first digit.

    i.e.

    =(LEFT(A2,1)&MID(A2,LEN(A2)/2+1,1)&RIGHT(A2,1))*1

    It may need refining also if the digits become 2 (i.e. 21.23.12), so you can use a formula to find the first period, then count left, and last period and count right, etc.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Getting close:

    You will need four helper columns (copy down, assume your data is in column A, starting at A2).

    Column B (for left item), in B2

    =(LEFT(A2,FIND(".",A2)-1))*10000

    Column C (for mid item) in C2

    =LEFT(RIGHT(A2,LEN(A2)-FIND(".",A2,1)),FIND(".",RIGHT(A2,LEN(A2)-FIND(".",A2,1)))-1)*100

    Column D, in D2

    =(RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,".","*",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))))*1

    Column E, in E2

    =SUM(B2: D2)

    copy down. Then in Cell F2,

    =MAX(E2:E16)

    Now, if you use dynamic named ranges, it will work for any number going down without changing the formulas.
    Attached Files Attached Files
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •