Combining Index, Match, Max and Abs functions 1

[jnowles]

I’m stumped. I have a table like below and want to paste in col H (col7) the column heading of the maximum of the absolute values of col1-col6. For example at note1 col 7 returns “col1” while at note12 col7 returns “col2”. I would then like to shade the cell (b12:G12) containing that max abs value
col1 col2 col3 col4 col5 col6
note1 0.1289 -0.0018 0.0550 -0.0573 0.0696 -0.0531
note2 -0.0181 0.2228 0.1183 -0.1510 -0.0591 0.2506
note3 0.0343 0.2273 0.0095 -0.0462 -0.1293 -0.0976
note4 -0.0050 0.1239 -0.0188 0.1461 0.1208 0.2842
note5 0.1587 -0.0460 0.0355 0.0506 -0.0228 0.0233
note6 0.0081 -0.1456 0.2167 0.1139 -0.0092 -0.1686
note10 0.0923 0.1325 -0.0945 0.1292 0.0508 0.0983
note11 0.0792 0.1356 -0.0014 0.1778 0.0622 0.1221
note12 0.0007 -0.2235 0.0320 0.0552 -0.0925 -0.0465

The following formula does everything except finding ABS(B12:G12)

=CELL("contents",INDEX($B$1:$G$1,MATCH(MAX(B12:G12),B12:G12,0)))

but need to generalize this uto a vba routine. Can you help?

 

[KenWright]

Assuming headings in Row 1 and first row of numerical data in row 2:-

From a formula perspective, the following pasted into H2 and copied down would give you your column headings:-

=INDEX($B$1:$G$1,,MATCH(MAX(ABS(B2:G2)),ABS(B2:G2),0))

array entered using CTRL+SHIFT+ENTER

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

One way with VBA:-

Sub FlagCells()

Dim r As Long
Dim LastRow As Long
Dim MaxVal As Double
Dim Maxheader As String
Dim Cel As Range
Dim RngOrig As Range

Application.ScreenUpdating = False

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To LastRow
   Set RngOrig = Cells(r, "B").Resize(1, 6)
   RngOrig.Interior.ColorIndex = xlNone
   
   MaxVal = Application.WorksheetFunction.Min(RngOrig)
   Maxheader = Range("B1").Value
   
   For Each Cel In RngOrig
      If Abs(Cel.Value) > MaxVal Then
         MaxVal = Abs(Cel.Value)
         Maxheader = Cel.Offset(1 - r, 0).Value
      End If
   Next Cel
   
   For Each Cel In RngOrig
      If Abs(Cel.Value) = MaxVal Then
         Cel.Interior.ColorIndex = 3
      End If
   Next Cel
   
   Cells(r, "H").Value = Maxheader
Next r

End Sub

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[jnowles]

Ken,

Thank you, excactly what I wanted in both cases. I'll need to look-up array formulas.

 

[KenWright]

In the formula approach, you could also possibly use Conditional Formatting to do the highlighting by having it check the Col header against the value in Col H, eg:-

Select the range B2:I20, do Format / Conditional formatting, change 'cell value' is to 'formula is', put in the formula =$H2=B$1, hit Enter and then hit the Format button and choose a red pattern. Hit Ok and you are done.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------