Advanced conditional formating in Excel

[CharlieMike73]

I have an excel sheet that has a list of computer names and tracks what versions of a piece of software are currently installed on a given machine.

Some machines have more than one version installed, while others have only one version.

What I need to do is highlight the row for latest version (highest version number) for each machine.

Desired Result:

[b]Machine Name[tab][tab]Product Name[tab][tab]Product Version[/b]
[highlight yellow]DT00DTA1LTHP031[tab][tab]mats client[tab][tab]7.0.4.0
DT00DTA1LTMO002[tab][tab]mats client[tab][tab]7.1.6.6
DT00DTA1LTMO035[tab][tab]mats client[tab][tab]7.1.6.6
DT00DTA1PCBP093[tab][tab]mats client[tab][tab]7.1.6.6
DT00DTA1PCIS041[tab][tab]mats client[tab][tab]7.1.6.6[/highlight]
DT00DTA1PCIS050[tab][tab]mats client[tab][tab]7.0.4.0
[highlight yellow]DT00DTA1PCIS050[tab][tab]mats client[tab][tab]7.1.6.6[/highlight]
DT00DTA1PCMO008[tab][tab]mats client[tab][tab]7.0.4.0
DT00DTA1PCMO008[tab][tab]mats client[tab][tab]7.1.3.0
DT00DTA1PCMO008[tab][tab]mats client[tab][tab]7.1.6.2
DT00DTA1PCMO008[tab][tab]mats client[tab][tab]7.1.6.4
[highlight yellow]DT00DTA1PCMO008[tab][tab]mats client[tab][tab]7.1.6.6
DT00DTA5PCTRG01[tab][tab]mats client[tab][tab]7.1.6.6[/highlight]
DT00DTA5PCTRG03[tab][tab]mats client[tab][tab]7.0.4.0
[highlight yellow]DT00DTA5PCTRG03[tab][tab]mats client[tab][tab]7.1.6.6[/highlight]
DT00DTA5PCTRG04[tab][tab]mats client[tab][tab]7.0.4.0
[highlight yellow]DT00DTA5PCTRG04[tab][tab]mats client[tab][tab]7.1.6.6[/highlight]
DT00DTA5PCTRG05[tab][tab]mats client[tab][tab]7.0.4.0
[highlight yellow]DT00DTA5PCTRG05[tab][tab]mats client[tab][tab]7.1.6.6
DT00DTA5PCTRG06[tab][tab]mats client[tab][tab]7.0.4.0[/highlight]
DT00DTA5PCTRG07[tab][tab]mats client[tab][tab]7.0.4.0
[highlight yellow]DT00DTA5PCTRG07[tab][tab]mats client[tab][tab]7.1.6.6
DT00DTA5PCTRG08[tab][tab]mats client[tab][tab]7.1.6.6
DT00DTA5PCTRG09[tab][tab]mats client[tab][tab]7.1.6.6[/highlight]
DT00DTA5PCTRG10[tab][tab]mats client[tab][tab]7.0.4.0
[highlight yellow]DT00DTA5PCTRG10[tab][tab]mats client[tab][tab]7.1.6.6[/highlight]
DT00DTA5PCTRG13[tab][tab]mats client[tab][tab]7.0.4.0
[highlight yellow]DT00DTA5PCTRG13[tab][tab]mats client[tab][tab]7.1.6.6
DT01DTC1PCMT008[tab][tab]mats client[tab][tab]7.1.6.6
DT01DTC1PCMT009[tab][tab]mats client[tab][tab]7.1.6.6[/highlight]

I looked at conditional formating but was not able to find how to only select the highest version number for each individual computer name.

Regards,
Charlie

 

[Salut39]

Hi Charlie,

Quick fix is to sort your data by "Machine Name" and then by "Product Version".

In D1: IF(A1=A2,0,VALUE(SUBSTITUTE(C1,".","")))

Copy down the formula and apply conditioning format for the whole table to highlight:

formula is: =$D2<>0

Yuri

 

[krispi]

Charlie - you could try this:

1: Sort your data by Machine Name and Product Version.
2: Assuming your data begins in cell A2, go to Format/Conditional Formatting. In Condition 1, select Formula Is, and type =$A2<>$A3 in the box.
3: Copy Cell A2, then Edit/Paste Special/Formats in the rest of your cells.

HTH,

Chris

 

[Salut39]

...or just use =IF(A1=A2,0,C1)

 

[SkipVought]

1. Sort by MachineName Asc, Produt Version Dec

2. CF
[tt]
=$C2=INDEX($C$2:$C$31,MATCH($A2,$A$2:$A$31,0),1)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]