Advice with Finding Data

purceld2

Well-known Member
Joined
Aug 18, 2005
Messages
586
Office Version
  1. 2013
Platform
  1. Windows
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.

Assignments_All.xlsx
ABCDEFGH
2TerritoryCampaignPublisherAssignedReturnedDays AssignedLast AllocatedStatus
3114/05/201625/06/2016421937Overdue
4105/08/201619/08/2016141882OK
5107/11/201626/12/2016491753Overdue
6108/03/201726/03/2017181663OK
7126/03/201707/05/2017421621Overdue
8129/07/201715/10/2017781460Overdue
9110/02/201824/02/2018141328OK
10121/07/201828/07/201871174OK
11101/11/201803/01/2019631015Overdue
12119/01/201925/02/201937962Overdue
13103/04/201918/04/201915910OK
14128/06/201928/07/201930809OK
15107/09/201918/09/201911757OK
16107/09/201918/09/201911757OK
17112/12/201904/01/202023649OK
181126/09/202006/12/202071312Overdue
191219/09/202125ALLOCATEDOK
20222/04/201608/07/2016771924Overdue
21206/08/201614/08/201681887OK
22203/10/201603/12/2016611776Overdue
23221/01/201715/03/2017531674Overdue
24229/03/201702/04/201741656OK
25212/05/201718/06/2017371579Overdue
26222/07/201730/07/201781537OK
27202/09/201706/10/2017341469Overdue
28206/12/201715/01/2018401368Overdue
Assignment
Cell Formulas
RangeFormula
F3:F28F3=IF(ISBLANK(E3),TODAY()-D3,E3-D3)
G3:G28G3=IF(ISBLANK(E3),"ALLOCATED",TODAY()-E3)
H3:H28H3=IF(F3>30,"Overdue","OK")
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
What about using a Pivot Table with Returned values set to 'Max'

21 10 14.xlsm
JKL
1
2Max of Returned
3TerritoryTotal
416/12/2020
5215/01/2018
6Grand Total6/12/2020
7
Latest


1634177576148.png
 
Upvote 0
Solution
Thanks for updating your profile. (y)
.. and glad to see that a Pivot Table seemed OK for you. :)
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top