How to Calculate and print MAX value from given data (urgent)

[eroztech]

Hello all,
I have to calculate the Max utilization of HDD partitions daily on hourly basis, below is the example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 222"]
<tbody>[TR]
[TD="class: xl66, width: 222"]17:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl66, width: 158"]18:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]19:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 222"]
<tbody>[TR]
[TD="class: xl66, width: 222"]20:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 273"]
<tbody>[TR]
[TD="class: xl66, width: 273"][TABLE="width: 273"]
<tbody>[TR]
[TD="class: xl66, width: 273"]21:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]partitions are below threshold[/TD]
[TD][TABLE="width: 158"]
<tbody>[TR]
[TD="class: xl66, width: 158"]65% /advdata/datashareB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl66, width: 108"]"/ " Partition 89%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 222"]
<tbody>[TR]
[TD="class: xl66, width: 222"]All the partitions are below threshold[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]61% /advdata/datashareB, 90%/advdata/core[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

in this case I write the [TABLE="width: 500"]
<tbody>[TR]
[TD]61% /advdata/datashareB, 90%/advdata/core[/TD]
[/TR]
</tbody>[/TABLE]
in my report because this cell have max percentage '90%'.
There are hundreds of servers so I need a formula to calculate this.
Please help.

 

[Jubjab]

is that how the data is set up? I.e. text data in the cells? One column for each hour and one row per server?

 

[eroztech]

is that how the data is set up? I.e. text data in the cells? One column for each hour and one row per server?

Yes you are right Jubjab...

 

[Jubjab]

I think a formula approach is impossible. Is VBA an alternative?

 

[Jubjab]

Here is a VBA alternative. Data starting on row 2 and in columns A:E. Adjust as needed:

Sub a()
Dim mytext As String
Dim resultarray As Variant
Dim a As Integer

For x = 2 To 200 'number of rows
myMax = 0
For a = 1 To 5 'number of columns

mytext = Range("a2").Offset(x - 2, 0 + a - 1).Value


hits = InStr(mytext, "%")

If hits = 0 Then GoTo continue:
If hits > 3 Then
    percentagevalues = Trim(Mid(mytext, hits - 3, 3))
    ElseIf hits = 3 Then percentagevalues = Trim(Mid(mytext, hits - 2, 2))
    Else: percentagevalues = Trim(Mid(mytext, hits - 1, 1))
End If

hits = InStr(hits + 1, mytext, "%")
If hits > 0 Then
percentagevalues = percentagevalues & "|" & Trim(Mid(mytext, hits - 3, 3))
End If

resultarray = Split(percentagevalues, "|")

  For i = LBound(resultarray) To UBound(resultarray)
    If resultarray(i) > myMax Then
      myMax = resultarray(i)
      largestcell = mytext
    End If
  Next i
  
continue:
 
Next a

Range("K" & x).Value = largestcell

Next x


End Sub