Forum Discussion

jomymathew's avatar
jomymathew
Copper Contributor
Nov 02, 2024

Return Matching Data from Most Recent Date based on condition

I am looking to return the result as Open or Close against the most recent date for each customer code. I have tried several formulas and combination of formulas.

 

Below is the data I'm working with. 

 

DateCustomer CodeArticle
01-01-2021101AA
01-02-2021101DD
01-03-2021101CC
01-04-2021101CC
01-05-2021101DD
01-01-2021201BB
01-02-2021201CC
01-03-2021201DD
01-04-2021201DD
01-05-2021201AA

 

If everything works well, below is the result I required. 

 

DateCustomer CodeArticleResult
01-01-2021101AAClose
01-02-2021101DDClose
01-03-2021101CCClose
01-04-2021101CCClose
01-05-2021101DDOpen
01-01-2021201BBClose
01-02-2021201CCClose
01-03-2021201DDClose
01-04-2021201DDClose
01-05-2021201AAOpen

 

Below is the formula I used to find out the article against latest date with respect to the customer code. 

 

=FILTER($C$2:$C$11,($A$2:$A$11=MAXIFS($A$2:$A$11,$B$2:$B$11,B2))*($B$2:$B$11=B2))

 

But once I incorporated if function for returning open/close, I'm getting the below result which is wrong as highlighted.

 

=IF(FILTER($C$2:$C$11,($A$2:$A$11=MAXIFS($A$2:$A$11,$B$2:$B$11,B2))*($B$2:$B$11=B2))=C2,"Open","Close")

 

DateCustomer CodeArticleResult
01-01-2021101AAClose
01-02-2021101DDOpen
01-03-2021101CCClose
01-04-2021101CCClose
01-05-2021101DDOpen
01-01-2021201BBClose
01-02-2021201CCClose
01-03-2021201DDClose
01-04-2021201DDClose
01-05-2021201AAOpen

 

 Any help would be appreciated, thank you!

Resources