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

ekalavya

New Member
Joined
Mar 5, 2014
Messages
31
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:

KLMN
A0.20.30.230.31
B0.60.10.540.33
C0.250.640.770.99
D0.230.440.720.51

<tbody>
</tbody>


The desired output in a second sheet:

AN0.31
BK0.6
CN0.99
DM0.72

<tbody>
</tbody>


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

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.


Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,211,789
Messages
6,103,985
Members
447,888
Latest member
eaydogan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top