Rank subgroups without duplicates

[Philip63]

Hi

I am looking to rank subgroups within excel. Each subgroup needs to be ranked without skipping numbers if their are duplicates.

I have attached a exemplar of the data. For each participant I want the years ranked in order without skipping numbers. The rank column shows the data I want the formula to calculate.

I am struggling to find the correct formula for this. Any help would much appreciated.

Thanks

Philip

[Glenn Kennedy]

Sorry I do not understand the logic behind this... You need to explain HOW you derived your expected results.

[Philip63]

Hi Glenn

My expected results in the rank column were manually inputted. Hopefully there is a formula to calculate this that I can use for multiple participants.

What I need the formula to do is:
- Separately rank each participant (treat each participant as a separate subgroup).
- For each participant rank the year that they provided data (the oldest year will receive a ranking of 1 and the most recent year will receive the highest numbered ranking in the sub-group).
- The formula should not skip ranks. (If the first two years for a participant are 1994's, both should be ranked 1 and the following year will be ranked 2).

Essentially I am trying to order the years that each participant provided data to allow me to conduct longitudinal analysis.

Hope this provides some clarity.

[Glenn Kennedy]

Clear!! I was focussing on the year, not the participant and was getting very muddled. back in 5 mins...

[Glenn Kennedy]

Hi. This will do it.

=SUMPRODUCT((B2>B$2:B$24)*($A$2:$A$24=A2)/COUNTIFS($A$2:$A$24,$A$2:$A$24,B$2:B$24,B$2:B$24))+1

[Philip63]

Thanks for that Glenn.

The formula appears to work. However, when I extend it to fit the 486687 rows of data I have by changing 24 in all the $A$24 and $B$24 to 486687, the calculation seems to return zero for each cell. I'm not sure if this is because the formula is too complex for the large data set I have, but it does show a message in excel saying "Calculating: (4 Processor(s)) : 0%".

Is there a reason why this formula may not be calculating correctly when I include more rows of data? Or is there a less complex formula that returns the same results?

Thanks for your help.

Phil

[Glenn Kennedy]

With 500,000 rows you will need to either: learn to be patient and wait, or get someone to write a VBA solution. You should have stated up front that you wanted a solution that would work for half a million rows!!

[sallame]

realy thank u soooooooooooooo much , but can doing it with vba ?


thank u again

[vasu2007]

Great solution brother. thank you very much