On 2002-03-08 07:58, JohnJay wrote:
Can anyone help with the following problem....I have Scores in Column C, and these scores are associated with data broken down into 3 subgroups in columns K, L, & M. Here is my problem...I just want to rank the scores in Column C that have the data in Columns K, L, & M in common. All this data is unsorted. Thank you all
John, the only way I can figure out how to do this is using a two step approach. Somewhere you have to specify the criteria that you are looking for in columns K, L, & M so that you can rank the scores. For my purposes, I put them in Q2, R2, and S2. In column D I put in an array formula to compare columns K, L, & M to the values in Q1, R1, and S1. The formula was (an array formula meaning you have to press CTRL+SHIFT+ENTER instead of ENTER):
=IF(((K1:M1)=($Q$1:$S$1))*C1=0,"",((K1:M1)=($Q$1:$S$1))*C1)
This would put the value of C1 in D1 if all three columns (K, L, & M) equal the values in Q1, R1, & S1. Then in E1 I put this formula and copied down:
=IF(ISERROR(RANK(D1,$D$1:$D$8)),"",RANK(D1,$D$1:$D$8))
My example was using data in rows 1 to 8. You'll have to change to the appropriate number.
I don't know if this will help you out, but it's all I could think of.
Regards,