Excel formula to rank based on multiple criteria

[adit_787]

Hey,

Please help, a little urgent!

Need a way to rank a table based on multiple criteria. This is what the table looks like

Appt	Schedule	Time lapsed	Urgent1	urgent2	rank
10	11/1/2015 21:00 <tbody> </tbody>	72			4
16	11/1/2015 23:00	70		Y	3
21	11/2/2015 04:00	65	Y		1
25	11/2/2015 15:00	54			5
30	11/3/2015 00:00	45	Y		2
31	11/3/2015 06:00	39		Y	6
35	11/3/2015 12:00	33			7

<tbody>
</tbody>

Trying to create a list of what should be prioritized first. The idea of ranking is anything that is Urgent 1 should be ranked first, depending on largest time lapsed, after which, anything with urgent 2 should get prioritized only if greater than 48 (time lapsed), after which the oldest should get ranked first.

I know a little complicated, trying to figure out for a couple days now and hitting deadline, any help appreciated!!!

Thanks in advance guys

 

[Oeldere]

Rearange the data like shown in the data under Original.

After that just sort on urgent, and second on time (from great to small).

Book1
	A	B	C	D
1	Appt	Schedule	Time lapsed	Urgent
2	21	11-2-2015 04:00	65	Urgent1
3	30	11-3-2015 00:00	45	Urgent1
4	16	11-1-2015 23:00	70	urgent2
5	31	11-3-2015 06:00	39	urgent2
6	10	11-1-2015 21:00	72
7	25	11-2-2015 15:00	54
8	35	11-3-2015 12:00	33
9
10		Original
11	Appt	Schedule	Time lapsed	Urgent
12	10	11-1-2015 21:00	72
13	16	11-1-2015 23:00	70	urgent2
14	21	11-2-2015 04:00	65	Urgent1
15	25	11-2-2015 15:00	54
16	30	11-3-2015 00:00	45	Urgent1
17	31	11-3-2015 06:00	39	urgent2
18	35	11-3-2015 12:00	33
Blad18

 

[adit_787]

Hey,

Thanks a lot! That solves all of the problems, except for 1

Urgent 2 has to be prioritized over non urgent, only if the time lapsed is greater than 48. Any ideas?

 

[Oeldere]

with a helpcolumn.

Book1
	A	B	C	D	E
43	Appt	Schedule	Time lapsed	Urgent	helpcolumn
44	21	11-2-2015 04:00	65	Urgent1	Urgent1
45	30	11-3-2015 00:00	45	Urgent1	Urgent1
46	16	11-1-2015 23:00	70	urgent2	urgent2
47	10	11-1-2015 21:00	72		urgent3
48	25	11-2-2015 15:00	54		urgent3
49	31	11-3-2015 06:00	39	urgent2	urgent3
50	35	11-3-2015 12:00	33		urgent3
51
52		Original
53	Appt	Schedule	Time lapsed	Urgent	helpcolumn
54	10	11-1-2015 21:00	72		urgent3
55	16	11-1-2015 23:00	70	urgent2	urgent2
56	21	11-2-2015 04:00	65	Urgent1	Urgent1
57	25	11-2-2015 15:00	54		urgent3
58	30	11-3-2015 00:00	45	Urgent1	Urgent1
59	31	11-3-2015 06:00	39	urgent2	urgent3
60	35	11-3-2015 12:00	33		urgent3
Blad18

Cell Formulas
Range	Formula
E44	=IF(D44="","urgent3",IF(AND(D44="urgent2",C44<48),"urgent3",D44))
E54	=IF(D54="","urgent3",IF(AND(D54="urgent2",C54<48),"urgent3",D54))

 

[adit_787]

Omg, that's perfect!! Thank you soooo much!!

 

[Oeldere]

You're welcome. We appreciate the feedback!