Ranking Dates and Grouping Together

[scottymelloty]

this is a sample of my data

15-01-06 xxxxx
15-01-06 xxxxx
15-01-06 xxxxx
20-01-06 xxxxx
20-01-06 xxxxx
20-01-06 xxxxx
27-01-06 xxxxx
27-01-06 xxxxx

i want to rank the dates so i can do some working outs from them , when i rank the dates its starts off at no1 for all the 15-01-06 and then goes to no4 for the 20-01-06 then no 7 for the 27-01-06 data ,

how can i rank them so it puts no1 for all 15-01 , then no2 for all 20-01 then no3 for 27-01 and so on

there will be about 150 entries with the same date every week and i want it to count the whole group of dates as 1 rank instead of it counting every date basically

or is there a better way , many thanks

[Bernie Deitrick]

Scott,

Use this array formula, entered using Ctrl-Shift-Enter:

=RANK(A2,$A$2:$A$11,TRUE)-(COUNTIF($A$2:$A$11,"<"&A2)-SUM((1/COUNTIF($A$2:$A$11,$A$2:$A$11))*($A$2:$A$11<A2)))

Adjust the ranges to suit.

HTH,
Bernie
MS Excel MVP


"scottymelloty" <scottymelloty.22tdoc_1139240105.1126@excelforum-nospam.com> wrote in message
news:scottymelloty.22tdoc_1139240105.1126@excelforum-nospam.com...
>
> this is a sample of my data
>
> 15-01-06 xxxxx
> 15-01-06 xxxxx
> 15-01-06 xxxxx
> 20-01-06 xxxxx
> 20-01-06 xxxxx
> 20-01-06 xxxxx
> 27-01-06 xxxxx
> 27-01-06 xxxxx
>
> i want to rank the dates so i can do some working outs from them , when
> i rank the dates its starts off at no1 for all the 15-01-06 and then
> goes to no4 for the 20-01-06 then no 7 for the 27-01-06 data ,
>
> how can i rank them so it puts no1 for all 15-01 , then no2 for all
> 20-01 then no3 for 27-01 and so on
>
> there will be about 150 entries with the same date every week and i
> want it to count the whole group of dates as 1 rank instead of it
> counting every date basically
>
> or is there a better way , many thanks
>
>
> --
> scottymelloty
> ------------------------------------------------------------------------
> scottymelloty's Profile: http://www.excelforum.com/member.php...fo&userid=3808
> View this thread: http://www.excelforum.com/showthread...hreadid=508940
>

[scottymelloty]

Thanks i have tried this but i am having problems

when i tried this i just get everything returning as 1

my date data is in ranges from A2:A942 and i have changed this in the formula , im not sure how to enter the data using shift+ctrl+enter , that dosnt seem to do anything, is that what i am doing wrong ?

many thanks

[Bernie Deitrick]

Scott,

Select the first cell with that formula, press F2 to enter edit mode, then
press and hold the Ctrl key, press and hold the Alt key and, with those two
keys still held down, press the Enter Key, similar to using Ctrl Alt Del to
reboot. Then the formula will be entered as an array formula. Copy down
the column to match your data, and all the cells won't return 1.

HTH,
Bernie
MS Excel MVP


"scottymelloty" <scottymelloty.22up4m_1139301600.3099@excelforum-nospam.com>
wrote in message
news:scottymelloty.22up4m_1139301600.3099@excelforum-nospam.com...
>
> Thanks i have tried this but i am having problems
>
> when i tried this i just get everything returning as 1
>
> my date data is in ranges from A2:A942 and i have changed this in the
> formula , im not sure how to enter the data using shift+ctrl+enter ,
> that dosnt seem to do anything, is that what i am doing wrong ?
>
> many thanks
>
>
> --
> scottymelloty
> ------------------------------------------------------------------------
> scottymelloty's Profile:
> http://www.excelforum.com/member.php...fo&userid=3808
> View this thread: http://www.excelforum.com/showthread...hreadid=508940
>

[scottymelloty]

Thanks, thats works a treat , a problem i seem ot have tho is that it keeps crashing excel when i use it on my large database, excel freezes altogether, no problems on a smaller sample of selections but every time crashes on the file i want to use it on as soon as i copy the formula down it says calculating cells and then freezes and nothing works, so i presume its the ammount of data im trying to rank , but thats another problem i guess.

thanks for your time and help