Query

[appalcrumble]

Hi All

Firstly, thanks for the the awesome help on this forum. I have used it numerous time to help solve problems.

I am however stumped (or too lazy) by the following:

My data is setup as follows:

<table border="0" cellpadding="0" cellspacing="0" width="448"><col style="width:48pt" span="7" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:48pt" height="20" width="64"> Data</td> <td class="xl64" style="width:48pt" align="right" width="64">A</td> <td class="xl64" style="width:48pt" align="right" width="64">B</td> <td class="xl64" style="width:48pt" align="right" width="64">C</td> <td class="xl64" style="width:48pt" align="right" width="64">D</td> <td class="xl64" style="width:48pt" align="right" width="64">E</td> <td class="xl64" style="width:48pt" align="right" width="64">F</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Points</td> <td align="right">36</td> <td align="right">30</td> <td align="right">32</td> <td align="right">36</td> <td align="right">30</td> <td align="right">30</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Rank</td> <td align="right">1</td> <td align="right">4</td> <td align="right">3</td> <td align="right">1</td> <td align="right">4</td> <td align="right">4</td> </tr> </tbody></table>
I would like to have a formula / vba function so that the resulting output looks as follows:

<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width:48pt" span="3" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">Output</td> <td style="width:48pt" width="64">
</td> <td style="width:48pt" width="64">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">1</td> <td class="xl67" align="right">A</td> <td align="right">36</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">
</td> <td class="xl67" align="right">D</td> <td align="right">36</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">3</td> <td class="xl67" align="right">C</td> <td align="right">32</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">4</td> <td class="xl67" align="right">B</td> <td align="right">30</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl67" align="right">E</td> <td align="right">30</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl67" align="right">F</td> <td align="right">30</td> </tr> </tbody></table>
Is there a way to easily do this?

Thanks in advance

 

[anandvarma]

sort on Rank
or transpose and sort on Rank

 

[anandvarma]

forgot to put formula

try this formula

=IF(COUNT(c$2:c2)=c2,c2,"")

after the transpose and sorting table will be in the following order
a b c
Data Points Rank

in the column d put this formula and drag it down