Loop down by group, Loop Across by condition

[Sayth]

This is omething that is fairly easy to do in a spreadsheet by hand. But I am not sure how to write it into a macro as it becomes part of a bigger macro.

IN the attached spreadsheet you can see what I am trying to acheive. I aim to use Percentrank by group and then across the columns. There are 29 conitions across(nothing big they have already been calculated) I just need to percentrank them.

My Groups can number from 1 to 10 however there will not usually be ten, for example in my sample spreadsheet, there are only two groups 6 & 8.

http://www.mediafire.com/file/ob0f25qwyc6j45h/Sample.xlsx

 

[Peter_SSs]

You are much more likely to get quick help from experienced board helpers if you post a small sample screen shot directly in your post. Many helpers here (me included) choose not to download files from another source, except occasionally as a last resort.

Excel jeanie
or
RichardSchollar’s beta HTML Maker - see the red text in his signature block at the bottom of his post
or
Borders-Copy-Paste

 

[Sayth]

okay will try but this sheet has 58 columns so might not be very helpful. Here is a link to screenshothttp://i988.photobucket.com/albums/af10/Sayth_Renshaw/excel.png

 

[Peter_SSs]

No you're right, that doesn't help much. There are two main reasons, ...

1. We cannot copy the data from that image and most of us don't like having to type heaps of test data before testing.

2. You haven't explained your requirement in relation to the image. The text in your first post doesn't immediately look like it applies to that image to me.

Did you try any of the screen shot methods I suggested above? One of the features of those is that data can be copied from them to a worksheet to test.

Also, if you could make up a small dummy file with a lot less groups/columns that still demonstrated your problem, that should help with keeping any screen shots small.

 

[Sayth]

<table border="0" cellpadding="0" cellspacing="0" width="1152"><col style="width: 48pt;" span="2" width="64"> <col style="width: 48pt;" span="16" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" height="20" width="64">number</td> <td class="xl66" style="width: 48pt;" width="64">ID</td> <td class="xl65" style="width: 48pt;" width="64">Con1</td> <td class="xl66" style="width: 48pt;" width="64">Con2</td> <td class="xl66" style="width: 48pt;" width="64">Con3</td> <td class="xl67" style="width: 48pt;" width="64">Con4</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Con5</td> <td class="xl66" style="width: 48pt;" width="64">Con6</td> <td class="xl66" style="width: 48pt;" width="64">Con7</td> <td class="xl67" style="width: 48pt;" width="64">Con8</td> <td class="xl66" style="width: 48pt;" width="64">PerCon1</td> <td class="xl66" style="width: 48pt;" width="64">Percon2</td> <td class="xl66" style="width: 48pt;" width="64">Percon3</td> <td class="xl66" style="width: 48pt;" width="64">Percon4</td> <td class="xl66" style="width: 48pt;" width="64">Percon5</td> <td class="xl66" style="width: 48pt;" width="64">Percon6</td> <td class="xl66" style="width: 48pt;" width="64">Percon7</td> <td class="xl67" style="width: 48pt;" width="64">Percon8</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">116318</td> <td class="xl68" align="right">886.6179</td> <td align="right">611.6129</td> <td align="right">0</td> <td class="xl69" align="right">0</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">1353.154</td> <td align="right">1180.238</td> <td class="xl69" align="right">676.9795</td> <td class="xl75" align="right">58.30%</td> <td class="xl75" align="right">75.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">75.00%</td> <td class="xl75" align="right">100.00%</td> <td class="xl78" align="right">83.30%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">130305</td> <td class="xl68" align="right">1100</td> <td align="right">0</td> <td align="right">0</td> <td class="xl69" align="right">3412.5</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">0</td> <td align="right">1137.931</td> <td class="xl69" align="right">1325</td> <td class="xl75" align="right">75.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">75.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">83.30%</td> <td class="xl78" align="right">83.30%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">103130</td> <td class="xl68" align="right">786.3434</td> <td align="right">492.4627</td> <td align="right">854.7015</td> <td class="xl69" align="right">2377.419</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">891.4159</td> <td align="right">771.2777</td> <td class="xl69" align="right">525.8621</td> <td class="xl75" align="right">50.00%</td> <td class="xl75" align="right">66.60%</td> <td class="xl75" align="right">58.30%</td> <td class="xl75" align="right">58.30%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">50.00%</td> <td class="xl75" align="right">41.60%</td> <td class="xl78" align="right">66.60%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">123105</td> <td class="xl68" align="right">1610.801</td> <td align="right">3466.667</td> <td align="right">0</td> <td class="xl69" align="right">3150</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">0</td> <td align="right">1133.459</td> <td class="xl69" align="right">0</td> <td class="xl75" align="right">66.60%</td> <td class="xl75" align="right">83.30%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">58.30%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">66.60%</td> <td class="xl78" align="right">0.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">124706</td> <td class="xl68" align="right">997.8261</td> <td align="right">0</td> <td align="right">2220</td> <td class="xl69" align="right">0</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">2550</td> <td align="right">613.6364</td> <td class="xl69" align="right">533.3333</td> <td class="xl75" align="right">58.30%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">91.60%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">83.30%</td> <td class="xl75" align="right">25.00%</td> <td class="xl78" align="right">58.30%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">109781</td> <td class="xl68" align="right">655.5946</td> <td align="right">1175.163</td> <td align="right">0</td> <td class="xl69" align="right">0</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">2470</td> <td align="right">1142.165</td> <td class="xl69" align="right">1563.025</td> <td class="xl75" align="right">16.60%</td> <td class="xl75" align="right">66.60%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">75.00%</td> <td class="xl75" align="right">58.30%</td> <td class="xl78" align="right">58.30%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">110174</td> <td class="xl68" align="right">784.9206</td> <td align="right">1066.667</td> <td align="right">0</td> <td class="xl69" align="right">1575</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">1300</td> <td align="right">310.8108</td> <td class="xl69" align="right">0</td> <td class="xl75" align="right">33.30%</td> <td class="xl75" align="right">58.30%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">33.30%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">66.60%</td> <td class="xl75" align="right">16.60%</td> <td class="xl78" align="right">0.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">121967</td> <td class="xl68" align="right">990</td> <td align="right">0</td> <td align="right">0</td> <td class="xl69" align="right">0</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">0</td> <td align="right">1066.81</td> <td class="xl69" align="right">0</td> <td class="xl75" align="right">33.30%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">41.60%</td> <td class="xl78" align="right">0.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">133384</td> <td class="xl68" align="right">619.4805</td> <td align="right">0</td> <td align="right">1381.439</td> <td class="xl69" align="right">0</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">0</td> <td align="right">1004.916</td> <td class="xl69" align="right">0</td> <td class="xl75" align="right">8.30%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">41.60%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">33.30%</td> <td class="xl78" align="right">0.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">118717</td> <td class="xl68" align="right">748.5062</td> <td align="right">462.4113</td> <td align="right">1663.636</td> <td class="xl69" align="right">0</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">1171.875</td> <td align="right">200.7009</td> <td class="xl69" align="right">153.1955</td> <td class="xl75" align="right">16.60%</td> <td class="xl75" align="right">33.30%</td> <td class="xl75" align="right">58.30%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">50.00%</td> <td class="xl75" align="right">8.30%</td> <td class="xl78" align="right">8.30%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">121205</td> <td class="xl68" align="right">502.1277</td> <td align="right">0</td> <td align="right">0</td> <td class="xl69" align="right">6400</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">0</td> <td align="right">750</td> <td class="xl69" align="right">385.7143</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">66.60%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">8.30%</td> <td class="xl78" align="right">8.30%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">138988</td> <td class="xl68" align="right">11300</td> <td align="right">0</td> <td align="right">0</td> <td class="xl69" align="right">11300</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl69" align="right">0</td> <td class="xl75" align="right">100.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">91.60%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl78" align="right">0.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">6</td> <td class="xl63" align="right">109969</td> <td class="xl68" align="right">730.5985</td> <td align="right">0</td> <td align="right">0</td> <td class="xl69" align="right">2091.667</td> <td class="xl68" style="border-left: medium none;" align="right">6275</td> <td align="right">2890</td> <td align="right">992.3532</td> <td class="xl69" align="right">395.4545</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">0.00%</td> <td class="xl75" align="right">41.60%</td> <td class="xl75" align="right">75.00%</td> <td class="xl75" align="right">58.30%</td> <td class="xl75" align="right">0.00%</td> <td class="xl78" align="right">0.00%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl79" style="height: 15pt;" align="right" height="20">8</td> <td class="xl76" align="right">95942</td> <td class="xl70" align="right">8319.525</td> <td class="xl64" align="right">7829.208</td> <td class="xl64" align="right">12021.83</td> <td class="xl71" align="right">6425</td> <td class="xl70" style="border-left: medium none;" align="right">11447.2</td> <td class="xl64" align="right">6581.319</td> <td class="xl64" align="right">5310.142</td> <td class="xl71" align="right">5252.997</td> <td class="xl64"> </td> <td class="xl64"> </td> <td class="xl64"> </td> <td class="xl64"> </td> <td class="xl64"> </td> <td class="xl64"> </td> <td class="xl64"> </td> <td class="xl71"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">113531</td> <td class="xl68" align="right">2578.723</td> <td align="right">0</td> <td align="right">1801.944</td> <td class="xl69" align="right">33300</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">0</td> <td align="right">3851.695</td> <td class="xl69" align="right">2470.833</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">125963</td> <td class="xl68" align="right">3494.599</td> <td align="right">4257.381</td> <td align="right">1588.438</td> <td class="xl69" align="right">4387.5</td> <td class="xl68" style="border-left: medium none;" align="right">6300</td> <td align="right">0</td> <td align="right">2456.293</td> <td class="xl69" align="right">2941.426</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">115804</td> <td class="xl68" align="right">3056.097</td> <td align="right">3962.267</td> <td align="right">0</td> <td class="xl69" align="right">0</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">0</td> <td align="right">1820.553</td> <td class="xl69" align="right">1658.844</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">109491</td> <td class="xl68" align="right">2086.552</td> <td align="right">2089.19</td> <td align="right">1580.645</td> <td class="xl69" align="right">3031.25</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">9275</td> <td align="right">2353.995</td> <td class="xl69" align="right">1816.977</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">98848</td> <td class="xl68" align="right">2404.522</td> <td align="right">2284.649</td> <td align="right">2702.857</td> <td class="xl69" align="right">2139.923</td> <td class="xl68" style="border-left: medium none;" align="right">5038.095</td> <td align="right">3629.834</td> <td align="right">1834.035</td> <td class="xl69" align="right">1694.31</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">118160</td> <td class="xl68" align="right">2745.719</td> <td align="right">3804.958</td> <td align="right">0</td> <td class="xl69" align="right">7172.941</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">2777.091</td> <td align="right">2235.356</td> <td class="xl69" align="right">1829.307</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">112124</td> <td class="xl68" align="right">1561.159</td> <td align="right">1263.741</td> <td align="right">2906.667</td> <td class="xl69" align="right">1718.182</td> <td class="xl68" style="border-left: medium none;" align="right">1850</td> <td align="right">7763.636</td> <td align="right">1496.162</td> <td class="xl69" align="right">1542.181</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">109322</td> <td class="xl68" align="right">1165.728</td> <td align="right">1074.419</td> <td align="right">2148.095</td> <td class="xl69" align="right">0</td> <td class="xl68" style="border-left: medium none;" align="right">2028.125</td> <td align="right">0</td> <td align="right">1141.07</td> <td class="xl69" align="right">895.3488</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">109173</td> <td class="xl68" align="right">2355.189</td> <td align="right">3581.018</td> <td align="right">1073.818</td> <td class="xl69" align="right">0</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">2698.98</td> <td align="right">1932.474</td> <td class="xl69" align="right">2161.17</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">118071</td> <td class="xl68" align="right">6198.602</td> <td align="right">5131.597</td> <td align="right">12737.5</td> <td class="xl69" align="right">0</td> <td class="xl68" style="border-left: medium none;" align="right">11300</td> <td align="right">14315</td> <td align="right">5104.901</td> <td class="xl69" align="right">4833.083</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">100515</td> <td class="xl68" align="right">1120.899</td> <td align="right">2194.137</td> <td align="right">0</td> <td class="xl69" align="right">2305</td> <td class="xl68" style="border-left: medium none;" align="right">0</td> <td align="right">1437.358</td> <td align="right">1562.191</td> <td class="xl69" align="right">2716.426</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl77" style="height: 15pt;" align="right" height="20">8</td> <td class="xl63" align="right">113483</td> <td class="xl68" align="right">1726.313</td> <td align="right">2825.455</td> <td align="right">1742.857</td> <td class="xl69" align="right">965.1515</td> <td class="xl68" style="border-left: medium none;" align="right">2825</td> <td align="right">1152.941</td> <td align="right">1112.19</td> <td class="xl69" align="right">2193.676</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td class="xl69"> </td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl80" style="height: 15.75pt;" align="right" height="21">8</td> <td class="xl81" align="right">96133</td> <td class="xl72" align="right">1730.688</td> <td class="xl73" align="right">2179.437</td> <td class="xl73" align="right">0</td> <td class="xl74" align="right">0</td> <td class="xl72" style="border-left: medium none;" align="right">2020.175</td> <td class="xl73" align="right">1495.08</td> <td class="xl73" align="right">1762.675</td> <td class="xl74" align="right">1653.85</td> <td class="xl73"> </td> <td class="xl73"> </td> <td class="xl73"> </td> <td class="xl73"> </td> <td class="xl73"> </td> <td class="xl73"> </td> <td class="xl73"> </td> <td class="xl74"> </td> </tr> </tbody></table>

 

[Sayth]

In the image you will see that there are a group ID individual ID and then a set of conditions with results for each condition.

I want to be able to use percentrank on each group so in this set of data this is Group 6 and Group 8. However Groups can number from 1 to 10. So for each individual condition I want to use percentrank by group.

I have started Percentrank on Group 6 to illustrate what I am trying to acheive.

 

[Sayth]

Any ideas I was trying to create a loop using a column range and an offset to acheive it but cannot get it too work. Its not the looping down via group thats the issue its looping across using percntrank for each column that I can't do.