Hi
I am look for a formula possibly a lookup to find the last row in a spreadsheet for each group with the latest return date.
I have attached some sample data. For each "Territory" I need to return the row of data with the latest "Returned" date. So it would be row 18 based for Territory 1 based on the 06/12/2020 being the latest date.
So need to do this for all the other Territories in the spreadsheet which currently goes up to 35.
I am look for a formula possibly a lookup to find the last row in a spreadsheet for each group with the latest return date.
I have attached some sample data. For each "Territory" I need to return the row of data with the latest "Returned" date. So it would be row 18 based for Territory 1 based on the 06/12/2020 being the latest date.
So need to do this for all the other Territories in the spreadsheet which currently goes up to 35.
Assignments_All.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
2 | Territory | Campaign | Publisher | Assigned | Returned | Days Assigned | Last Allocated | Status | ||
3 | 1 | 14/05/2016 | 25/06/2016 | 42 | 1937 | Overdue | ||||
4 | 1 | 05/08/2016 | 19/08/2016 | 14 | 1882 | OK | ||||
5 | 1 | 07/11/2016 | 26/12/2016 | 49 | 1753 | Overdue | ||||
6 | 1 | 08/03/2017 | 26/03/2017 | 18 | 1663 | OK | ||||
7 | 1 | 26/03/2017 | 07/05/2017 | 42 | 1621 | Overdue | ||||
8 | 1 | 29/07/2017 | 15/10/2017 | 78 | 1460 | Overdue | ||||
9 | 1 | 10/02/2018 | 24/02/2018 | 14 | 1328 | OK | ||||
10 | 1 | 21/07/2018 | 28/07/2018 | 7 | 1174 | OK | ||||
11 | 1 | 01/11/2018 | 03/01/2019 | 63 | 1015 | Overdue | ||||
12 | 1 | 19/01/2019 | 25/02/2019 | 37 | 962 | Overdue | ||||
13 | 1 | 03/04/2019 | 18/04/2019 | 15 | 910 | OK | ||||
14 | 1 | 28/06/2019 | 28/07/2019 | 30 | 809 | OK | ||||
15 | 1 | 07/09/2019 | 18/09/2019 | 11 | 757 | OK | ||||
16 | 1 | 07/09/2019 | 18/09/2019 | 11 | 757 | OK | ||||
17 | 1 | 12/12/2019 | 04/01/2020 | 23 | 649 | OK | ||||
18 | 1 | 1 | 26/09/2020 | 06/12/2020 | 71 | 312 | Overdue | |||
19 | 1 | 2 | 19/09/2021 | 25 | ALLOCATED | OK | ||||
20 | 2 | 22/04/2016 | 08/07/2016 | 77 | 1924 | Overdue | ||||
21 | 2 | 06/08/2016 | 14/08/2016 | 8 | 1887 | OK | ||||
22 | 2 | 03/10/2016 | 03/12/2016 | 61 | 1776 | Overdue | ||||
23 | 2 | 21/01/2017 | 15/03/2017 | 53 | 1674 | Overdue | ||||
24 | 2 | 29/03/2017 | 02/04/2017 | 4 | 1656 | OK | ||||
25 | 2 | 12/05/2017 | 18/06/2017 | 37 | 1579 | Overdue | ||||
26 | 2 | 22/07/2017 | 30/07/2017 | 8 | 1537 | OK | ||||
27 | 2 | 02/09/2017 | 06/10/2017 | 34 | 1469 | Overdue | ||||
28 | 2 | 06/12/2017 | 15/01/2018 | 40 | 1368 | Overdue | ||||
Assignment |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F3:F28 | F3 | =IF(ISBLANK(E3),TODAY()-D3,E3-D3) |
G3:G28 | G3 | =IF(ISBLANK(E3),"ALLOCATED",TODAY()-E3) |
H3:H28 | H3 | =IF(F3>30,"Overdue","OK") |