Results 1 to 8 of 8

031208, 11:41 #1Registered User
 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

031208, 18:00 #2Registered User
 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

031308, 06:37 #3Registered User
 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

031308, 06:48 #4Registered User
 Join Date
 Jul 2003
 Location
 Amsterdam, Nederland
 Posts
 450
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 dir´s
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 i´ve got open office the dutch version
so the functions are all F.C.U.K.ed upGreetz 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

031308, 08:56 #5Registered User
 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

031308, 09:11 #6Registered User
 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 nowLast edited by Marvels; 031308 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

031308, 15:45 #7Registered User
 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

031308, 17:33 #8Registered User
 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.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