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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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
 

ekalavya

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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Hmm, maybe possible.

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

Howard
 

ekalavya

New Member
Joined
Mar 5, 2014
Messages
31

ADVERTISEMENT

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:

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

New Member
Joined
Mar 5, 2014
Messages
31

ADVERTISEMENT

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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,583
Members
417,223
Latest member
jelena_

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
Top