10 applicants to 25 available vacancies .... help

[Samerqatar]

Dear Genius fellows
I am facing a challenging data sorting project

I have a 10 person applying for positions ( 25 Available positions)
Each person will have to submit 5 options sorting with priority of selection from 1 to 5
each person have a different birth date and we want to give the priority to the older persons to choose first

I need a formula that will handle this database so the oldest person obviously will win his first option
The second oldest person will win his first option if not already chosen by the first person otherwise he will win his second option
The third person will win his first option if never choose before otherwise his second option if never chosen by 1st and 2nd person ,,otherwise he will win his 3rd option
and going on things will get more complicated until some persons have all their 5 options were taken so it should indicate so invalid result.

Data will look like below :

Name Hire date 1st 2nd 3rd 4th 5th
John 04-04-1977 I L O H J
Mark 09-05-1977 F L O K J
David 07-06-1977 I K G B H
Samantha 08-06-1977 J F N H A
Sara 08-09-1977 I N G D J
sue 14-02-1978 F K I J G
Jack 21-02-1978 M O I E K
lean 09-03-1978 L C D M E
sanjive 09-05-1978 O C B H J
Ali 15-08-1978 B E M A D

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Samerqatar]

Thank you Glenn ,

attached the document ,

I look forward to your support as this is very important to me at my work

[davsth]

sort the date by DOB
j2 = e2
j3=IF(COUNTIF(J$2:J2,E3)=0,E3,IF(COUNTIF(J$2:J2,F3)=0,F3,IF(COUNTIF(J$2:J2,G3)=0,G3,IF(COUNTIF(J$2:J2,H3)=0,H3,IF(COUNTIF(J$2:J2,I3)=0,I3,"Can't Allocate")))))

and copy down

[Samerqatar]

Thank you very much Davsth ,

is there a way to do it without sorting by DOB ? , i mean by leaving the data as is in mixed DOB sequence .. can we make a condition to recognize the DOB priority