MAX / IF formula to return max "text" value - always returns zero? (XL2007)

[AOB]

Hi all,

I have a sheet of data with two columns I want to run a formula against.

The first column contains device names, the second, version numbers of a piece of software.

Both are text (the device name is alphanumeric and the software version is numerical but with major, minor and subminor versions separated by periods, i.e. 2.1.1234.6789)

Devices appear multiple times in the list

So for example if the "table" looks like this :

[TABLE="width: 500"]
<tbody>[TR]
[TD]Device
[/TD]
[TD]Version
[/TD]
[/TR]
[TR]
[TD]ABCD1234EFGH
[/TD]
[TD]2.1.1234.5678
[/TD]
[/TR]
[TR]
[TD]ZYXW9876VUTS
[/TD]
[TD]2.1.1234.5678
[/TD]
[/TR]
[TR]
[TD]ABCD1234EFGH
[/TD]
[TD]2.2.2345.6789
[/TD]
[/TR]
[TR]
[TD]ABCD1234EFGH
[/TD]
[TD]2.1.1234.5678
[/TD]
[/TR]
</tbody>[/TABLE]

I'm looking for a formula (on a separate sheet of unique/distinct devices), to return the latest version recorded against any given device :

[TABLE="width: 500"]
<tbody>[TR]
[TD]Device
[/TD]
[TD]Latest Version
[/TD]
[/TR]
[TR]
[TD]ABCD1234EFGH
[/TD]
[TD]2.2.2345.6789
[/TD]
[/TR]
[TR]
[TD]ZYXW9876VUTS
[/TD]
[TD]2.1.1234.5678
[/TD]
[/TR]
</tbody>[/TABLE]

I've tried using MAX / IF, like this (for simplicity, I've named the columns on the source data sheet "Devices" and "Versions" respectively)

{=MAX(IF(Devices=$A2,Versions))}

But it only ever returns zero?

When I evaluate the array formula, I can see it "detecting" the appropriate matches :

MAX({"2.1.1234.5678";FALSE;"2.2.2345.6789";"2.1.1234.5678";FALSE;FALSE;....})

But it's like all those "falses" trip up the Max function and it just evaluates to zero?

Really frustrating and I'm sure it's something stupid I've not taken into consideration (my guess is the text nature of the version!) but can't figure it out

Any suggestions??

Thanks!!

 

[Max1616]

Try this:

{=MAX(IF(A:A=A2,SUBSTITUTE(B:B,".","")+0,0))}

This is an array formula. Put everything between the brackets into the cell then press control+shift+enter

You will have to insert the "." back into the value. If the above code is in cell C2, then I would put this in D2 to add the "." back in:

=CONCATENATE(LEFT(C2,1),".",MID(C2,2,1),".",MID(C2,3,4),".",RIGHT(C2,4))

 

[Max1616]

The issue with yours is you cannot take the "Max" of a text field. What I did was remove the periods from the string, then added zero to force excel to view these as numbers instead of string.

 

[AOB]

Fantastic!

The issue with yours is you cannot take the "Max" of a text field

This was definitely the bit I was unsure of, I thought (hoped?) the array formula would be able to order text numerically but evidently not

Had to tweak it slightly as some of the rows have blanks in the version column but it now works with the following :

=MAX(IF(Devices=$A2,IFERROR(SUBSTITUTE(Versions,".","")+0,0)))

Thanks so much Max (if ever there was somebody who was going to help me with a MAX function...!)