DOUBLE FILTER function to return ONLY records with most recent date when base data is sorted continuously

E

elcalvo

New Member
Joined
Dec 15, 2023
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  • I'm using Office 365
  • I have a defined table named exampledata with headers and its data below
  • In another sheet (called “check”), I have made a filter section for Partners in A2 with inserted checkboxes (insert – cell controls)
  • The formula filters on "Meeting" in column F (Type) so it returns only rows with "Meeting" and only shows the most recent date as per column G (Last) AND that filters on the ticked boxes from the Partners filter section (see previous bullet)
  • The data in exampledata is continuously being sorted (on #, on Partner, on Company, on Prio) depending on the activity that I am doing. This is been done with a VBA code: doubleclicking on the header sorts the data in that column first (and then the rest of the data)


The output of the result table (the LET result) gives a different result when it comes to sorting in column 7 (“Last) depending on the exampledata sorting.



How to overcome this ?



To Do.xlsm
ABCDEFGHIJKLM
2#PartnerClassCompanyProductTypeLastPrioXNextDueAction/notesInclude Partner
31Partner 1OtherCompany AOtherMeeting31/01/20231TRUE02/02/2023Notes 1TRUE
42Partner 1OtherCompany AOtherMeeting22/03/20231TRUE06/08/2023Notes 2TRUE
53Partner 2OtherCompany DOtherMeeting09/07/20231TRUE09/07/2023Notes 3TRUE
64Partner 3OtherCompany EOtherAction09/07/20231TRUE09/07/2023Notes 4TRUE
75Partner 2OtherCompany FOtherMeeting11/07/20231TRUE13/07/2023Notes 5TRUE
86Partner 2OtherCompany DOtherMeeting23/07/20231TRUE25/07/2023Notes 6TRUE
98Partner 1OtherCompany AOtherAction04/08/20231TRUE04/08/2023Notes 8TRUE
107Partner 3OtherCompany GOtherMeeting04/08/20231TRUE04/08/2023Notes 7TRUE
119Partner 1OtherCompany BOtherAction06/08/20231TRUE06/08/2023Notes 9TRUE
1215Partner 1OtherCompany BOtherMeeting06/08/20231TRUE06/08/2023Notes 9TRUE
1314Partner 1OtherCompany AOtherAction07/09/20231TRUE04/08/2023Notes 8TRUE
1411Partner 3OtherCompany AOtherMeeting30/11/20231TRUE04/08/2023Notes 8TRUE
1510Partner 1OtherCompany COtherMeeting01/12/20231TRUE24/03/2023Notes 10TRUE
1616Partner 1OtherCompany COtherMeeting01/12/20231TRUE24/03/2023Notes 10TRUE
1712Partner 3OtherCompany BOtherAction01/12/20231TRUE06/08/2023Notes 9TRUE
1813Partner 3OtherCompany COtherMeeting01/12/20231TRUE24/03/2023Notes 10TRUE
Sheet10
Cell Formulas
RangeFormula
M3:M8,M11M3=XLOOKUP(Sheet10!$B3,check!$A$2#,check!$B$2:$B$4)
M9M9=XLOOKUP(Sheet10!$B10,check!$A$2#,check!$B$2:$B$4)
M10,M14M10=XLOOKUP(Sheet10!$B9,check!$A$2#,check!$B$2:$B$4)
M12M12=XLOOKUP(Sheet10!$B17,check!$A$2#,check!$B$2:$B$4)
M13M13=XLOOKUP(Sheet10!$B16,check!$A$2#,check!$B$2:$B$4)
M15,M17:M18M15=XLOOKUP(Sheet10!$B12,check!$A$2#,check!$B$2:$B$4)
M16M16=XLOOKUP(Sheet10!$B18,check!$A$2#,check!$B$2:$B$4)


To Do.xlsm
AB
1Partner
2Partner 1
3Partner 2
4Partner 3
check
Cell Formulas
RangeFormula
A2:A4A2=SORT(UNIQUE(Sheet10!$B$3:$B$18))
Dynamic array formulas.


To Do.xlsm
ABCDEFGHIJKLM
6#PartnerClassCompanyProductTypeLastPrioXNextDueAction/notesInclude Partner
711Partner 3OtherCompany AOtherMeeting30/11/20231TRUE04/08/20230Notes 8TRUE
86Partner 2OtherCompany DOtherMeeting23/07/20231TRUE25/07/20230Notes 6TRUE
95Partner 2OtherCompany FOtherMeeting11/07/20231TRUE13/07/20230Notes 5TRUE
107Partner 3OtherCompany GOtherMeeting04/08/20231TRUE04/08/20230Notes 7TRUE
1115Partner 1OtherCompany BOtherMeeting06/08/20231TRUE06/08/20230Notes 9TRUE
1210Partner 1OtherCompany COtherMeeting01/12/20231TRUE24/03/20230Notes 10TRUE
check
Cell Formulas
RangeFormula
A6:M12A6=LET( a, FILTER(exampledata,(exampledata[Type] = "Meeting")*(exampledata[Include Partner])), uc, UNIQUE(INDEX(a,0,4)), b, SORT(SORT(a, 4, ), 7, -1), c,INDEX(b,0,4), result,REDUCE(exampledata[#Headers],uc,LAMBDA(a,i,VSTACK(a,TAKE(FILTER(b,c=i),1)))), result )
Dynamic array formulas.


To Do.xlsm
ABCDEFGHIJKLM
2#PartnerClassCompanyProductTypeLastPrioXNextDueAction/notesInclude Partner
31Partner 1OtherCompany AOtherMeeting31/01/20231TRUE02/02/2023Notes 1TRUE
42Partner 1OtherCompany AOtherMeeting22/03/20231TRUE06/08/2023Notes 2TRUE
53Partner 2OtherCompany DOtherMeeting09/07/20231TRUE09/07/2023Notes 3TRUE
64Partner 3OtherCompany EOtherAction09/07/20231TRUE09/07/2023Notes 4TRUE
75Partner 2OtherCompany FOtherMeeting11/07/20231TRUE13/07/2023Notes 5TRUE
86Partner 2OtherCompany DOtherMeeting23/07/20231TRUE25/07/2023Notes 6TRUE
97Partner 3OtherCompany GOtherMeeting04/08/20231TRUE04/08/2023Notes 7TRUE
108Partner 1OtherCompany AOtherAction04/08/20231TRUE04/08/2023Notes 8TRUE
119Partner 1OtherCompany BOtherAction06/08/20231TRUE06/08/2023Notes 9TRUE
1210Partner 1OtherCompany COtherMeeting01/12/20231TRUE24/03/2023Notes 10TRUE
1311Partner 3OtherCompany AOtherMeeting30/11/20231TRUE04/08/2023Notes 8TRUE
1412Partner 3OtherCompany BOtherAction01/12/20231TRUE06/08/2023Notes 9TRUE
1513Partner 3OtherCompany COtherMeeting01/12/20231TRUE24/03/2023Notes 10TRUE
1614Partner 1OtherCompany AOtherAction07/09/20231TRUE04/08/2023Notes 8TRUE
1715Partner 1OtherCompany BOtherMeeting06/08/20231TRUE06/08/2023Notes 9TRUE
1816Partner 1OtherCompany COtherMeeting01/12/20231TRUE24/03/2023Notes 10TRUE
Sheet10
Cell Formulas
RangeFormula
M3:M18M3=XLOOKUP(Sheet10!$B3,check!$A$2#,check!$B$2:$B$4)


To Do.xlsm
ABCDEFGHIJKLM
6#PartnerClassCompanyProductTypeLastPrioXNextDueAction/notesInclude Partner
711Partner 3OtherCompany AOtherMeeting30/11/20231TRUE04/08/20230Notes 8TRUE
86Partner 2OtherCompany DOtherMeeting23/07/20231TRUE25/07/20230Notes 6TRUE
95Partner 2OtherCompany FOtherMeeting11/07/20231TRUE13/07/20230Notes 5TRUE
107Partner 3OtherCompany GOtherMeeting04/08/20231TRUE04/08/20230Notes 7TRUE
1110Partner 1OtherCompany COtherMeeting01/12/20231TRUE24/03/20230Notes 10TRUE
1215Partner 1OtherCompany BOtherMeeting06/08/20231TRUE06/08/20230Notes 9TRUE
check
Cell Formulas
RangeFormula
A6:M12A6=LET( a, FILTER(exampledata,(exampledata[Type] = "Meeting")*(exampledata[Include Partner])), uc, UNIQUE(INDEX(a,0,4)), b, SORT(SORT(a, 4, ), 7, -1), c,INDEX(b,0,4), result,REDUCE(exampledata[#Headers],uc,LAMBDA(a,i,VSTACK(a,TAKE(FILTER(b,c=i),1)))), result )
Dynamic array formulas.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
The only difference I can see, is that the rows are in a different order, which is to be expected.
What exactly are you after?
 
Upvote 0
the rows are in a different order, but I expected that it would always sort the dates in column 7 "Last" in the result table. This gives me the overview of the last meetings with the unique Company. If there is no way to keep the order in the result table from changing, I need to find a different solution

the exampledata is used as a worksheet and continuously filled and sorted; the result table is used as a quick overview without the need to apply more filters and actions and ideally giving the overview with the "Last" always sorted
 
Upvote 0
What order do you want it in? You can sort the data as part of the final formula
 
Upvote 0
In that case use
Excel Formula: 
=LET(a, FILTER(exampledata,(exampledata[Type] = "Meeting")*(exampledata[Include Partner])),uc, UNIQUE(INDEX(a,0,4)),b, SORT(SORT(a, 4, ), 7, -1),c,INDEX(b,0,4),result,REDUCE(exampledata[#Headers],uc,LAMBDA(a,i,VSTACK(a,TAKE(FILTER(b,c=i),1)))),sort(result,7,-1))
 
Upvote 0
Solution
Fluff said:
In that case use
Excel Formula: 
=LET(a, FILTER(exampledata,(exampledata[Type] = "Meeting")*(exampledata[Include Partner])),uc, UNIQUE(INDEX(a,0,4)),b, SORT(SORT(a, 4, ), 7, -1),c,INDEX(b,0,4),result,REDUCE(exampledata[#Headers],uc,LAMBDA(a,i,VSTACK(a,TAKE(FILTER(b,c=i),1)))),sort(result,7,-1))
Click to expand...
absolutely fantastic ! thank you very much
works like a charm
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Fluff said:
In that case use
Excel Formula: 
=LET(a, FILTER(exampledata,(exampledata[Type] = "Meeting")*(exampledata[Include Partner])),uc, UNIQUE(INDEX(a,0,4)),b, SORT(SORT(a, 4, ), 7, -1),c,INDEX(b,0,4),result,REDUCE(exampledata[#Headers],uc,LAMBDA(a,i,VSTACK(a,TAKE(FILTER(b,c=i),1)))),sort(result,7,-1))
Click to expand...
@Fluff in case I want to make it descending and change the 1 to -1, also the headers get sorted which I don't want. What would be the fix for this ?
 
Upvote 0
How about
Excel Formula: 
=LET(a, FILTER(exampledata,(exampledata[Type] = "Meeting")*(exampledata[Include Partner])),uc, UNIQUE(INDEX(a,0,4)),b, SORT(SORT(a, 4, ), 7, -1),c,INDEX(b,0,4),result,DROP(REDUCE("",uc,LAMBDA(a,i,VSTACK(a,TAKE(FILTER(b,c=i),1)))),1),VSTACK(exampledata[#Headers],sort(result,7,-1)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
DOUBLE FILTER function to return ONLY records with most recent date
Replies
2
Views
359
elcalvo
E
E
  • Solved
FILTER function to return ONLY records with most recent date
Replies
5
Views
495
Fluff
Fluff
Z
  • Solved
Count Distinct Values in Filtered Table across Multiple Columns
Replies
6
Views
434
Fluff
Fluff
GMC The Macro Man
  • Solved
If Then Count calculation on Questionnaire
Replies
7
Views
431
Fluff
Fluff
K
  • Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
217
XLfriend777
XLfriend777

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

Share this page

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