Simple Rank Macro

[alocke]

Hi,
Just a simple thing - If I want to Rank data in cells A1,A3,A5,A7 amongst one another and also cells A2,A4,A6,A8 too.

The code I'm doing is more complex than this, but if someone can tell me how to do this then I'll be sorted. I think.

Cheers,
Alex

 

[alocke]

Sorry scrap all that, thats not the reason. Its to do with my working out I'll get back to you when I figure it out.
Cheers.

 

[alocke]

Brilliant it works. Thank you so much. I now have to do one more thing, I don't know whether if its better to include it in this macro or write it as a new one.
When I get the percentrank I want to subtract the last value from the third last value. If this causes a value greater than 0.15 I want the whole of row U of this certain dataset to appear in "sheet5", if its less than 0.15 to appear in "sheet6" If its neither than to not be put into either sheets.

There will be multiple datasets which will match either of these criteria, so is it possible for a dataset to take a column each? So the first dataset will appear in column A, the second in column B etc.

Also the title of the dataset is to be above these values. So if I've defined the firstrw as U8, and the title is in A4 does that mean I need to use offset(-4, -20) for each array?

This is my final question, sorry I've dragged this on for so long!
Alex

 

[Peter_SSs]

I'm not sure I have my head around that anyway, but what happens if the last value or the third last value is one of the null strings ("") because the corresponding cell in column U was blank or an error?

Can you confirm that the PERCENTRANK formula in column V?

 

[alocke]

I had to redo various formulae and if the data didn't change due to the formula change I removed it. So there will be no Null string values in the calculation.

 

[alocke]

and yes the percent rank was in column V.

 

[Peter_SSs]

You are right about the offset to get the heading. I've used -21 for the columns though since I am measuring from the formula column (V).

I've built a little bit of flexibility into this code, but not that offset for the heading. I've fixed that at 4 rows and 21 columns. If it needs flexibility perhaps we can add that later.

Make sure the 'Const' line values match your workbook details.

I've made the assumption that the comparison between the last value and the third last value is to be a test of the absolute value of the difference. That is if those 2 values were 0.9 and 0.7 then that would come under the "greater than 0.15" rule no matter which one of the values was last and which was third last. If that is incorrect, then just remove the Abs() from the Select Case line.

Anyway, test this in a copy of your workbook and see how it goes.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Move_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> ar <SPAN style="color:#00007F">As</SPAN> Range, Dest5 <SPAN style="color:#00007F">As</SPAN> Range, Dest6 <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> SrcSht <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Rank" <SPAN style="color:#007F00">'<-- Name of sheet with percentrank formulas</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> PRcol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "V" <SPAN style="color:#007F00">'<-- PercentRank column</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> fr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 8 <SPAN style="color:#007F00">'<-- First row</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Dest5 = Sheets("Sheet5").Cells(2, Columns.Count).End(xlToLeft)<br>    <SPAN style="color:#00007F">Set</SPAN> Dest6 = Sheets("Sheet6").Cells(2, Columns.Count).End(xlToLeft)<br>    <SPAN style="color:#00007F">With</SPAN> Sheets(SrcSht)<br>        lr = .Cells(Rows.Count, PRcol).End(xlUp).Row<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ar <SPAN style="color:#00007F">In</SPAN> .Range(PRcol & fr).Resize(lr - fr + 1) _<br>                .SpecialCells(xlFormulas).Areas<br>            <SPAN style="color:#00007F">With</SPAN> ar<br>                rws = .Rows.Count<br>                <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Abs(.Cells(rws - 2, 1).Value - .Cells(rws, 1).Value)<br>                    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> > 0.15<br>                        <SPAN style="color:#00007F">Set</SPAN> Dest5 = Dest5.Offset(, 1)<br>                        Dest5.Resize(rws).Value = ar.Offset(, -1).Value<br>                        Dest5.Offset(-1).Value = _<br>                            ar.Cells(1, 1).Offset(-4, -21).Value<br>                    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 0.15<br>                        <SPAN style="color:#00007F">Set</SPAN> Dest6 = Dest6.Offset(, 1)<br>                        Dest6.Resize(rws).Value = ar.Offset(, -1).Value<br>                        Dest6.Offset(-1).Value = _<br>                            ar.Cells(1, 1).Offset(-4, -21).Value<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ar<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[alocke]

Hi peter. It all ran perfectly on my test workbook. However when I run it on the real thing, it doesn't work. The ranking stage that is.

I think the problem is because #N/A , #NUM, #DIV/0 etc etc. Are included in the actual formula. What I mean by that is, when they come up in a cell are not ranked between the range. But when a value that does exist comes up, it is ranked between the range which has values that don't exist. Which therefore results in the whole column having undefined values.

Is there additional code I could add in order to make my range ignore these values? Or should this already be the case?
Thanks,

 

[Peter_SSs]

I'm afraid I no longer have a good idea of what you actually have in your sheet. Could you make up a dummy sheet with say 3 small groups of about 5 rows each and set them up so they have all the scenarios that can happen with your data. Then post the data and formulas (the first formula in each group should do) with Excel jeanie of the Board's HTML maker (see my signature block for links).

 

[alocke]

So if we have to percentrank A8,A208,A408,A608,A808 between one another. U8 = 0 U208 = 10 U408 = 11 U608 = #N/A U808 = -2

If we were to run the Rank macro between these cells, U608 would be ignored, so V608 would be a blank cell.

A problem occurs because when say U8 is ranked against the range, U608 is included in this range, even though it doesn't exist. So therefore cell V8 will come up as #N/A

Does this make better sense?

I couldn't use excel genie - it didn't work on my Mac, so sorry about that.
Thanks.

 

[Peter_SSs]

So if we have to percentrank A8,A208,A408,A608,A808 between one another. U8 = 0 U208 = 10 U408 = 11 U608 = #N/A U808 = -2

If we were to run the Rank macro between these cells, U608 would be ignored, so V608 would be a blank cell.

A problem occurs because when say U8 is ranked against the range, U608 is included in this range, even though it doesn't exist. So therefore cell V8 will come up as #N/A

Does this make better sense?

Not really ..

- I thought that was dealt with by my suggestion at b) in post #26.

- My question about blanks/errors and your response in posts 33 and 34 also seemed to indicate that this issue had been dealt with.

I couldn't use excel genie - it didn't work on my Mac, so sorry about that.

I don't use a Mac but my understanding is that you are correct and that there isn't an easy way of showing screen shots & formulas from a Mac. Pity.