Determining latest software version

[NewOrderFac33]

Good afternoon - just wondering if anyone has ever been asked to determine the latest version of a piece of software where the list contains 4 octet versions e.g.
Product A 1.0.2.3
Product A 1.0.3.5
Product A 1.1.0.1

In this case, the third item in the list would be returned

if the solution could also work for items with less than four octets e.g.

Product B 1.2.1
Product B 1.3
Product B 2.0

would return 2.0

Thanks

 

[DanteAmor]

Try:

Dante Amor
	A	B	C	D	E
1	PRODUCT	VERSION		PRODUCT	LATEST VERSION
2	Product A	1.0.2.3		Product A	1.1.0.1
3	Product A	1.0.3.5		Product B	2.0
4	Product A	1.1.0.1
5	Product B	1.2.1
6	Product B	1.3
7	Product B	2.0
Hoja1

Cell Formulas
Range	Formula
E2:E3	E2	=INDEX($B$2:$B$7,MAX(((MAX(($A$2:$A$7=D2)* ((LEFT($B$2:$B$7,FIND(".",$B$2:$B$7))&SUBSTITUTE(MID($B$2:$B$7,FIND(".",$B$2:$B$7)+1,99),".",""))*1)))= ((LEFT($B$2:$B$7,FIND(".",$B$2:$B$7))&SUBSTITUTE(MID($B$2:$B$7,FIND(".",$B$2:$B$7)+1,99),".",""))*1))* ROW($B$2:$B$7))-ROW($B$2)+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

If the formula is too long, you can try a helper column:

Dante Amor
	A	B	C	D	E
1	PRODUCT	VERSION	AUX	PRODUCT	LATEST VERSION
2	Product A	1.0.2.3	1.023	Product A	1.1.0.1
3	Product A	1.0.3.5	1.035	Product B	2.0
4	Product A	1.1.0.1	1.101
5	Product B	1.2.1	1.21
6	Product B	1.3	1.3
7	Product B	2.0	2
Hoja1

Cell Formulas
Range	Formula
E2:E3	E2	=INDEX($B$2:$B$7,MATCH(LOOKUP(2,1/($A$2:$A$7=D2),$C$2:$C$7),$C$2:$C$7))
C2:C7	C2	=(LEFT(B2,FIND(".",B2))&SUBSTITUTE(MID(B2,FIND(".",B2)+1,99),".",""))*1

 

[NewOrderFac33]

Really, sorry - I can't get this to work at all, even with the helper column Every cell in the array returns the same value I don't suppose there's a VBA solution that I could try, with the data laid out similar to the above.

 

[DanteAmor]

I could also help you with VBA but I need to see exactly how your data is in the sheet and the expected results.

As you can see in your examples and my examples the results are correct, so the data you are testing with is probably not like your initial sample.

That is why you must provide a larger and more real sample of your data.

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[MARK858]

Really, sorry - I can't get this to work at all, even with the helper column Every cell in the array returns the same value I don't suppose there's a VBA solution that I could try, with the data laid out similar to the above.

Did you confirm the first formula with Ctrl-Shift + Enter and not just Enter?

 

[Dave Patton]

Try the following
the 000 pads the number for a length of at least 4
the -- double negative converts the text to a number

T202308a.xlsm
	A	B	C	D
1	PRODUCT	VERSION		Version
2	Product A	1.0.2.3	1023	1.1.0.1
3	Product A	1.0.3.5	1035
4	Product A	1.1.0.1	1101
5	Product B	1.2.1	1210	2
6	Product B	1.3	1300
7	Product B	2	2000
8
2b

Cell Formulas
Range	Formula
D2,D5	D2	=INDEX(B2:B4,MATCH(MAX(C2:C4),C2:C4,0))
C2:C7	C2	=--LEFT(SUBSTITUTE(B2,".","")&"000",4)

 

[Dave Patton]

N.B. You can copy the post to a clean sheet.
click the icon below the f(x) in the header
move to your sheet, select cell A1, and paste

T202308a.xlsm
	A	B	C	D	E
1	PRODUCT	VERSION			Latest Version
2	Product A	1.0.2.3	1023	Product A	1.1.0.1
3	Product A	1.0.3.5	1035
4	Product A	1.1.0.1	1101
5	Product B	1.2.1	1210	Product B	2
6	Product B	1.3	1300
7	Product B	2	2000
2b

Cell Formulas
Range	Formula
D2,D5	D2	=A2
E2,E5	E2	=INDEX(B2:B4,MATCH(MAX(C2:C4),C2:C4,0))
C2:C7	C2	=--LEFT(SUBSTITUTE(B2,".","")&"000",4)

 

[NewOrderFac33]

N.B. You can copy the post to a clean sheet.
click the icon below the f(x) in the header
move to your sheet, select cell A1, and paste

T202308a.xlsm
	A	B	C	D	E
1	PRODUCT	VERSION			Latest Version
2	Product A	1.0.2.3	1023	Product A	1.1.0.1
3	Product A	1.0.3.5	1035
4	Product A	1.1.0.1	1101
5	Product B	1.2.1	1210	Product B	2
6	Product B	1.3	1300
7	Product B	2	2000
2b

Cell Formulas
Range	Formula
D2,D5	D2	=A2
E2,E5	E2	=INDEX(B2:B4,MATCH(MAX(C2:C4),C2:C4,0))
C2:C7	C2	=--LEFT(SUBSTITUTE(B2,".","")&"000",4)

 

[NewOrderFac33]

Good evening, Dave - thanks for your reply - I may be being really stupid here, but how do the formulae in column E refer to the product names in column D in order to work out the latest versions for Products A and B?

 

[NewOrderFac33]

Did you confirm the first formula with Ctrl-Shift + Enter and not just Enter?

I did, yes