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.

 

[ekalavya]

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

It throws the error "Type Mismatch", and I am currently outside to troubleshoot the error. I will get back to you soon after doing further research as on how to fix it.
Thanks a lot for your help so far.

 

[L. Howard]

Here is a sample workbook, Button 1 runs the code, returns shown on both sheets.

https://www.dropbox.com/s/6owup4tu5p3ijdw/HEADER of column with VALUE Drop Box.xlsm?dl=0

Howard