Dear all,
I have been laboring with this problem for quite a few days now, I am putting together a list of the 7 highest values, their row headers and their column headers.
Using Table 1 below, rows A to F and columns H to M, I have found the 6 largest values using the =Large function, see Table 2.
What I need to do now, is to find the corresponding headers, for both rows and columns.
The big issue turns out to be the fact that there are duplicates in my data set, every time there is a duplicate, it only returns the header of the first value.
I have considered doing a workaround, finding the CELL address of the =LARGE values, to then use the address for looking up row and column headers.
Alas, I cannot even get that to work. (I tried using different combinations of =CELL("address";Large(...)) but I can't seem to get it right)
I hope one you can assist me in my endeavors, I am still fairly new to the inner workings of Excel and any help is greatly appreciated!
Best regards, Rune
Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]row header[/TD]
[TD]Value from =LARGE(1,2,3,4,5,6...)[/TD]
[TD]column header[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have been laboring with this problem for quite a few days now, I am putting together a list of the 7 highest values, their row headers and their column headers.
Using Table 1 below, rows A to F and columns H to M, I have found the 6 largest values using the =Large function, see Table 2.
What I need to do now, is to find the corresponding headers, for both rows and columns.
The big issue turns out to be the fact that there are duplicates in my data set, every time there is a duplicate, it only returns the header of the first value.
I have considered doing a workaround, finding the CELL address of the =LARGE values, to then use the address for looking up row and column headers.
Alas, I cannot even get that to work. (I tried using different combinations of =CELL("address";Large(...)) but I can't seem to get it right)
I hope one you can assist me in my endeavors, I am still fairly new to the inner workings of Excel and any help is greatly appreciated!
Best regards, Rune
Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]row header[/TD]
[TD]Value from =LARGE(1,2,3,4,5,6...)[/TD]
[TD]column header[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]