Related Cell Reference Game Over

[Excel Zan]

Hello All,

I have attached a sheet and I need to know how I can get the last day alive as it is different for each player but relatively close by the game over date. There are about 500 players so manual work seems a little daunting. Please help, so what I was thinking was to get the Game over date, highlight via filtering, then I want excel to highlight above that cell a different color so I can filter by the color and get the last date alive for each player. Unfortunately, they may have gotten ammo or additional health/gill, therefore, it may not find the last day alive. I can delete those entries it need be, what is the best way to get this done?
File is below:

Player	Action	Date
A	10 Day 1	17-Jul-2015
A	Exrox Ammo 27 Weeks	17-Jul-2015
A	11 Day 1	07-Aug-2015
A	12 Day 1	28-Aug-2015
A	13 Day 1	18-Sep-2015
A	Exrox Ammo 36 Weeks	21-Sep-2015
A	14 Day 1	09-Oct-2015
A	15 Day 1	30-Oct-2015
A	16 Day 1	20-Nov-2015
A	Exrox Ammo 45 Weeks	17-Nov-2015
A	17 Day Alive	11-Dec-2015
A	18 Day Alive	30-Dec-2015
A	19 Day Alive	22-Jan-2016
A	20 Day Alive	12-Feb-2016
A	Exrox Ammo 57 Weeks	09-Feb-2016
A	21 Day Alive	04-Mar-2016
A	22 Day Alive	25-Mar-2016
A	23 Day Alive	15-Apr-2016
A	Game Over	06-May-2016
A	Addional Gill	06-May-2016
A	Extra Health
B	Game Start	20-Jan-2015
B	AliveDay 1	10-Feb-2015
B	2 Day 1	03-Mar-2015
B	3 Day 1	24-Mar-2015
B	4 Day 1	15-Apr-2015
B	Exrox Ammo 9 Weeks	18-Apr-2015
B	5 Day 1	27-May-2015
B	6 Day 1	17-Jun-2015
B	7 Day 1	08-Jul-2015
B	Exrox Ammo 18 Weeks	13-Jun-2015
B	8 Day 1	28-Jul-2015
B	9 Day 1	18-Aug-2015
B	10 Day 1	09-Sep-2015
B	Exrox Ammo 27 Weeks	17-Aug-2015
B	11 Day 1	29-Sep-2015
B	12 Day 1	20-Oct-2015
B	13 Day 1	10-Nov-2015
B	Exrox Ammo 36 Weeks	19-Oct-2015
B	14 Day 1	01-Dec-2015
B	15 Day 1	22-Dec-2015
B	16 Day 1	15-Jan-2016
B	Exrox Ammo 45 Weeks	21-Dec-2015
B	17 Day Alive	02-Feb-2016
B	18 Day Alive	23-Feb-2016
B	19 Day Alive	15-Mar-2016
B	20 Day Alive	05-Apr-2016
B	Exrox Ammo 57 Weeks	22-Feb-2016
B	21 Day Alive	26-Apr-2016
B	22 Day Alive	17-May-2016
B	23 Day Alive	10-Jun-2016
B	Exrox Ammo 69 Weeks	16-May-2016
B	Extra Health

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Muhammad_Usman]

You can just piked out the last day alive like :

Input :

Player	Action	Date
A	10 Day 1	17-Jul-15
A	Exrox Ammo 27 Weeks	17-Jul-15
A	11 Day 1	7-Aug-15
A	12 Day 1	28-Aug-15
A	13 Day 1	18-Sep-15
A	Exrox Ammo 36 Weeks	21-Sep-15
A	14 Day 1	9-Oct-15
A	15 Day 1	30-Oct-15
A	16 Day 1	20-Nov-15
A	Exrox Ammo 45 Weeks	17-Nov-15
A	17 Day Alive	11-Dec-15
A	18 Day Alive	30-Dec-15
A	19 Day Alive	22-Jan-16
A	20 Day Alive	12-Feb-16
A	Exrox Ammo 57 Weeks	9-Feb-16
A	21 Day Alive	4-Mar-16
A	22 Day Alive	25-Mar-16
A	23 Day Alive	15-Apr-16
A	Game Over	6-May-16
A	Addional Gill	6-May-16
A	Extra Health
B	10 Day 1	17-Jul-15
B	Exrox Ammo 27 Weeks	17-Jul-15
B	11 Day 1	7-Aug-15
B	12 Day 1	28-Aug-15
B	13 Day 1	18-Sep-15
B	Exrox Ammo 36 Weeks	21-Sep-15
B	14 Day 1	9-Oct-15
B	15 Day 1	30-Oct-15
B	16 Day 1	20-Nov-15
B	Exrox Ammo 45 Weeks	17-Nov-15
B	17 Day Alive	11-Dec-15
B	18 Day Alive	30-Dec-15
B	19 Day Alive	22-Jan-16
B	Game Over	25-Jan-16

<tbody>
</tbody>

Out Put

	Action	Date
A	23 Day Alive	15-Apr-16
B	19 Day Alive	22-Jan-16
C

<tbody>
</tbody>

Formula : F2

Its Array formula and enter with CTRL+SHIFT+ENTER

=INDEX(B:B,MATCH(1,(A:A=E2)*(B:B="Game Over"),0)-1)

In G2 =

=INDEX(C:C,MATCH(1,(A:A=E2)*(B:B="Game Over"),0)-1)

 

[Excel Zan]

Hello,

Thanks for such a quick reply.
Can I ask you what E2 is?
I only have columns A B C

 

[Muhammad_Usman]

Hello,

Thanks for such a quick reply.
Can I ask you what E2 is?
I only have columns A B C

E is the cloumn where Player name is written

E F G

	Action	Date
A	23 Day Alive	15-Apr-16
B	19 Day Alive	22-Jan-16
C

<colgroup><col><col><col></colgroup><tbody>
</tbody>