Forum Discussion
jomymathew
Nov 02, 2024Copper Contributor
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.
| Date | Customer Code | Article |
| 01-01-2021 | 101 | AA |
| 01-02-2021 | 101 | DD |
| 01-03-2021 | 101 | CC |
| 01-04-2021 | 101 | CC |
| 01-05-2021 | 101 | DD |
| 01-01-2021 | 201 | BB |
| 01-02-2021 | 201 | CC |
| 01-03-2021 | 201 | DD |
| 01-04-2021 | 201 | DD |
| 01-05-2021 | 201 | AA |
If everything works well, below is the result I required.
| Date | Customer Code | Article | Result |
| 01-01-2021 | 101 | AA | Close |
| 01-02-2021 | 101 | DD | Close |
| 01-03-2021 | 101 | CC | Close |
| 01-04-2021 | 101 | CC | Close |
| 01-05-2021 | 101 | DD | Open |
| 01-01-2021 | 201 | BB | Close |
| 01-02-2021 | 201 | CC | Close |
| 01-03-2021 | 201 | DD | Close |
| 01-04-2021 | 201 | DD | Close |
| 01-05-2021 | 201 | AA | Open |
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")
| Date | Customer Code | Article | Result |
| 01-01-2021 | 101 | AA | Close |
| 01-02-2021 | 101 | DD | Open |
| 01-03-2021 | 101 | CC | Close |
| 01-04-2021 | 101 | CC | Close |
| 01-05-2021 | 101 | DD | Open |
| 01-01-2021 | 201 | BB | Close |
| 01-02-2021 | 201 | CC | Close |
| 01-03-2021 | 201 | DD | Close |
| 01-04-2021 | 201 | DD | Close |
| 01-05-2021 | 201 | AA | Open |
Any help would be appreciated, thank you!
8 Replies
- LorenzoSilver Contributor
- jomymathewCopper ContributorI'm unable to use HSTACK in my version. Is there any alternate option you can help with. Thanks in advance.
- LorenzoSilver ContributorWhat version of Excel do you run?