generating a rank order on two variables

[rekoop]

Hello,
Is this possible...

I want excel to scan column A which contains course codes
some of which will be the same.
Then I want excel to compare the same course codes with
the corresponding entry date entered in Column B.
In column C I would like excel to generate a numbered
list base on same course code and the date entered.

This is basically a course waiting list that will assign
a priority number to an individual who wishes to be
placed on a course waiting list based on the date they
made the request.

Any suggestions are appreciated,

Rekoop

 

[hgrove]

rekoop wrote...
...

I want excel to scan column A which contains course codes some
of which will be the same. Then I want excel to compare the
same course codes with the corresponding entry date entered
in Column B. In column C I would like excel to generate a
numbered list base on same course code and the date entered.

This is basically a course waiting list that will assign a priority
number to an individual who wishes to be placed on a course
waiting list based on the date they made the request.

...

Presumably your list also includes a column holding student IDs for th
individuals making such requests; otherwise, your list wouldn't be abl
to assign priorities reliably to the students making the requests. S
I'll address the problem assuming that col C is the ID of the studen
making the request, and the priority number would be in column D
Further, I'll assume the range containing this table is A1:C25 wit
column headings in row 1.

Enter the following.

D1:
Priority

D2:
=1+SUMPRODUCT(--($A$2:$A$25=A2),--($B$2:$B$25<B2))
+SUMPRODUCT(--(A$1:A1=A2),--(B$1:B1=B2))

Fill D2 down into D325. Copy A125 and paste special as values i
blank range elsewhere, then sort that copy first on course number the
on priority.

Note that the D2 formula uses position on original list to resolve tie
between students requesting the same course on the same day. Even i
you recorded time of day, it's still possible that two students coul
submit requests at exactly the same time, so some form of tie-breakin
is needed

 

[Tushar Mehta]

An alternative to the function-based solution would be to sort the data
by columns A, the course id, as the primary key and B, the date, as the
secondary key. This will give you a complete list of all courses and
all wait-list dates. To restrict to a single course, use a auto-filter
based on A.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions