Find the highest value in row, print column header and value

[ekalavya]

Hello all,

I am hoping someone can help me with my following problem. I have a 300 by 300 (row x column) matrix. For each row, I would like to find the column with the highest value, and print the row name, name of the column with the highest value, and the highest value in the second sheet.

For example, input dataset is:

	K	L	M	N
A	0.2	0.3	0.23	0.31
B	0.6	0.1	0.54	0.33
C	0.25	0.64	0.77	0.99
D	0.23	0.44	0.72	0.51

<tbody>
</tbody>

The desired output in a second sheet:

A	N	0.31
B	K	0.6
C	N	0.99
D	M	0.72

<tbody>
</tbody>

I would very much appreciate your help. Thank you.
Please ignore the box outlines... this site generated them for some reason.

 

[L. Howard]

Try this in a standard module.
I assume KN is the 300th column from K, and adjust the sheet names as necessary.

Writes to column A and B on second sheet, easily changed.

Option Explicit

Sub Column_Large()

  Dim lRowCount&
  lRowCount = Sheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row
  
  With Sheets("Sheet2").Range("A2").Resize(lRowCount - 1)
    .Formula = "=INDEX(Sheet1!$K$1:$KN$1,MATCH(LARGE(Sheet1!K2:KN2,1),Sheet1!K2:KN2,0))": .Value = .Value
  End With
  With Sheets("Sheet2").Range("B2").Resize(lRowCount - 1)
    .Formula = "=LARGE(Sheet1!K2:KN2,1)": .Value = .Value
  End With
End Sub

 

[ekalavya]

Try this in a standard module.
I assume KN is the 300th column from K, and adjust the sheet names as necessary.

Writes to column A and B on second sheet, easily changed.

Hi L. Howard,

Your code works beautifully, & thank you for providing it. Though, I have ran into one more problem now.

The results show that there is one particular column (for example, say column M) seems to be the one with the largest value for most rows. I would like to avoid this.

Therefore, would it be possible to print in Sheet2 not one largest value, but say the top 15 or 20 values (and their respective column names, just as before) for each row? Column B&C would have the first largest, D&E would have the second largest, and so on up to the 15th largest.

 

[L. Howard]

Hmm, maybe possible.

With 300 rows, what happens when you get to the 20th largest?

Howard

 

[ekalavya]

Hmm, maybe possible.

With 300 rows, what happens when you get to the 20th largest?

Howard

Howard, the idea is to get a unique column with the highest value for each row. It did not occur to me before that the same column could have higher values for several rows. So, when I get to the 20th largest, my hope is that I can assign to each row a unique column. And I would do this manually.

Perhaps, I can reword my problem in two steps analysis:

a) get the column name as well as the highest value for each row (like before)
b) check if the column name repeats for the second row. If it repeats, get another column name (and the value, of course) that has the highest value (this would be the second highest value for this row, since the first column is not common anymore). And do this for all rows.

I hope I did not make it sound more complicated.

 

[L. Howard]

Okay, I think I got it.

So the gist is to NOT repeat the column and only increment the LARGE value if the column repeats. Then back to LARGE until a repeat column again.

Howard

 

[ekalavya]

Okay, I think I got it.

So the gist is to NOT repeat the column and only increment the LARGE value if the column repeats. Then back to LARGE until a repeat column again.

Howard

Yes. Unique column for each row, that also has the highest value for that row.

 

[L. Howard]

Yes. Unique column for each row, that also has the highest value for that row.

Can that be possible?

Say:

Large value in row 1 is in column Header10

Large value in row 2 is in column Header10

We now need to find a different value for row 2 to prevent Header10 from repeating.
So, the new value for row 2 cannot be the Large for row 2, it has to be the second Large to avoid returning Header10 again.

Is that correct?

Howard

 

[ekalavya]

Can that be possible?
Is that correct?
Howard

Absolutely! Thanks!

 

[L. Howard]

Give this a try, paste in a standard module.

My testing was on comparatively small amounts of data.

I believe it may be somewhat sensitive to blanks, perhaps in K2:KN2, as that produces a value for the variable LCol.

I have some confidence it will work on your 300 by 300 grid. If not, then I don't know where to turn.

Howard

Option Explicit

Sub Test_Columns_Large()
'/ by Claus @ MS Public
Dim i As Long, j As Long, LCol As Long
Dim varOut() As Variant
Dim valL As Long, valH

LCol = Application.CountA(Sheets("Sheet1").Range("K2:KN2"))

With Sheets("Sheet2")
    ReDim Preserve varOut(LCol - 1, 1)
    varOut(0, 1) = Evaluate("=Max(Sheet1!K2:KN2)")
    varOut(0, 0) = Evaluate("=INDEX(Sheet1!$K$1:$KN$1,MATCH(Max(Sheet1!K2:KN2),Sheet1!K2:KN2,0))")

    For i = 1 To LCol - 1
        For j = 1 To LCol + 1
            valL = Evaluate("=LARGE(Sheet1!K" & i + 2 & ":KN" & i + 2 & "," & j & ")")
            valH = Evaluate("=INDEX(Sheet1!$K$1:$KN$1,Match(" & valL & ",Sheet1!K" & i + 2 & ":KN" & i + 2 & ",0))")
            If valH <> varOut(i - 1, 0) Then
                varOut(i, 0) = valH
                varOut(i, 1) = valL
                Exit For
            End If
        Next j
    Next i
    .Range("A2").Resize(LCol, 2) = varOut
End With
End Sub