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

 

[Dave Patton]

Re Post #9
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?
They do not use the description from Column D.

Did the formulas work for you?
Try reviewing the formula with Excel's Formulas Evaluate Formula.

 

[Dave Patton]

You can hide Column C if you want.

T202308a.xlsm
	A	B	C	D
1	PRODUCT	VERSION	Helper Column	Latest 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
2b

Cell Formulas
Range	Formula
D2	D2	=INDEX(B2:B4,MATCH(MAX(C2:C4),C2:C4,0))
C2:C4	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)

Ah - I see now - your formulae refer to the specific row ranges for each product - in your example, Product A is rows 2-4, Product B is rows 5-7. My problem is that each product may have a completely different number of rows, making it (while not impossible) a bit impractical as I'd need to know the row ranges for each product - and there are 181 of them!

My Products are in A2:A4950 (range "Versions_Names"), the versions are in B2:B4950 (range "Versions_Versions"). the versions returned by your helper column are in C2:C4950 (range "Versions_Helpers") and I have a list of unique product names in H2:H180 (range "Versions_Unique_Names").

I want to have the largest version number for each Product (from C2:C4950/"Versions_Helpers" ) returned in I2:I180. I also don't mind a VBA solution, if you can think of one!

Thank you for your help!

 

[NewOrderFac33]

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.

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.

Good evening - I can't post live data from work, unfortunately - however:

My Products are in A2:A4950 (range "Versions_Names"), the versions are in B2:B4950 (range "Versions_Versions"). the versions returned by Dave Patton's helper column are in C2:C4950 (range "Versions_Helpers") and I have a list of unique product names in H2:H180 (range "Versions_Unique_Names").

I want to have the largest version number for each Product (from C2:C4950/"Versions_Helpers" ) returned in I2:I180.

Thank you for your help!

 

[Dave Patton]

Data Subtotal feature of Excel will add the formulas for the calculations by category.
You could then use a lookup formula to put the results where you want.
Put Max for the category Helper Column

T202308a.xlsm
	A	B	C
1	PRODUCT	VERSION	Helper Column
2	Product A	1.0.2.3	1023
3	Product A	1.0.3.5	1035
4	Product A	1.1.0.1	1101
5	Product A	0	1101
6	Product B	1.2.1	1210
7	Product B	1.3	1300
8	Product B	2	2000
9	Product B	2	2000
10	Grand	2	2000
2bb

Cell Formulas
Range	Formula
B9:C9,B5:C5	B5	=SUBTOTAL(4,B2:B4)
C2:C4,C6:C8	C2	=--LEFT(SUBSTITUTE(B2,".","")&"000",4)
B10:C10	B10	=SUBTOTAL(4,B2:B8)