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:

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

 

[Muhammad_Usman]

You can just piked out the last day alive like :

Input :

[TABLE="width: 266"]
<tbody>[TR]
[TD]Player[/TD]
[TD]Action[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10 Day 1[/TD]
[TD="align: right"]17-Jul-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Exrox Ammo 27 Weeks[/TD]
[TD="align: right"]17-Jul-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]11 Day 1[/TD]
[TD="align: right"]7-Aug-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]12 Day 1[/TD]
[TD="align: right"]28-Aug-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]13 Day 1[/TD]
[TD="align: right"]18-Sep-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Exrox Ammo 36 Weeks[/TD]
[TD="align: right"]21-Sep-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]14 Day 1[/TD]
[TD="align: right"]9-Oct-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]15 Day 1[/TD]
[TD="align: right"]30-Oct-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]16 Day 1[/TD]
[TD="align: right"]20-Nov-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Exrox Ammo 45 Weeks[/TD]
[TD="align: right"]17-Nov-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]17 Day Alive[/TD]
[TD="align: right"]11-Dec-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]18 Day Alive[/TD]
[TD="align: right"]30-Dec-15[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]19 Day Alive[/TD]
[TD="align: right"]22-Jan-16[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20 Day Alive[/TD]
[TD="align: right"]12-Feb-16[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Exrox Ammo 57 Weeks[/TD]
[TD="align: right"]9-Feb-16[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]21 Day Alive[/TD]
[TD="align: right"]4-Mar-16[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]22 Day Alive[/TD]
[TD="align: right"]25-Mar-16[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]23 Day Alive[/TD]
[TD="align: right"]15-Apr-16[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Game Over[/TD]
[TD="align: right"]6-May-16[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Addional Gill[/TD]
[TD="align: right"]6-May-16[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Extra Health[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]10 Day 1[/TD]
[TD="align: right"]17-Jul-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Exrox Ammo 27 Weeks[/TD]
[TD="align: right"]17-Jul-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]11 Day 1[/TD]
[TD="align: right"]7-Aug-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]12 Day 1[/TD]
[TD="align: right"]28-Aug-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]13 Day 1[/TD]
[TD="align: right"]18-Sep-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Exrox Ammo 36 Weeks[/TD]
[TD="align: right"]21-Sep-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]14 Day 1[/TD]
[TD="align: right"]9-Oct-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]15 Day 1[/TD]
[TD="align: right"]30-Oct-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]16 Day 1[/TD]
[TD="align: right"]20-Nov-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Exrox Ammo 45 Weeks[/TD]
[TD="align: right"]17-Nov-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]17 Day Alive[/TD]
[TD="align: right"]11-Dec-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]18 Day Alive[/TD]
[TD="align: right"]30-Dec-15[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]19 Day Alive[/TD]
[TD="align: right"]22-Jan-16[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Game Over[/TD]
[TD="align: right"]25-Jan-16[/TD]
[/TR]
</tbody>[/TABLE]

Out Put

[TABLE="width: 266"]
<tbody>[TR]
[TD][/TD]
[TD]Action[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]23 Day Alive[/TD]
[TD="align: right"]15-Apr-16[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]19 Day Alive[/TD]
[TD="align: right"]22-Jan-16[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="width: 266"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Action[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]23 Day Alive[/TD]
[TD="align: right"]15-Apr-16[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]19 Day Alive[/TD]
[TD="align: right"]22-Jan-16[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]