Forum Discussion
How to identify the most recent transaction
Hello,
I have a transaction table where serial numbers may be listed more than once if they were sold, returned and sold again. I need a way to create a column to identify the most recent transaction for each serial number so I can filter by the identifier and exclude transactions that are not the most recent in my data set. Below is a sample:
| BRAND | Invoice Date | Sales # | Model # | ITEM_DESC | Qty | SERIAL_NBR |
| WIDGIT | 6/20/2019 | R123 | Model 1 | Unit XYZ | -1 | Serial 1 |
| WIDGIT | 5/20/2019 | S123 | Model 1 | Unit XYZ | 1 | Serial 1 |
| WIDGIT | 11/29/2017 | S124 | Model 1 | Unit XYZ | 1 | Serial 1 |
| WIDGIT | 11/29/2017 | S125 | Model 3 | Unit XYZ | 1 | Serial 3 |
| WIDGIT | 1/15/2020 | S130 | Model 4 | Unit XYZ | 1 | Serial 4 |
| WIDGIT | 12/1/2019 | R126 | Model 4 | Unit XYZ | -1 | Serial 4 |
| WIDGIT | 9/20/2019 | S126 | Model 4 | Unit XYZ | 1 | Serial 4 |
| WIDGIT | 9/20/2019 | S127 | Model 5 | Unit XYZ | 1 | Serial 5 |
| WIDGIT | 10/15/2019 | S128 | Model 6 | Unit XYZ | 1 | Serial 6 |
Thank you in advance.
szub_2 -
If you're willing to try Power Query this can easily be done. This post describes the method https://excelgorilla.com/power-bi/power-query/reach-underlying-rows-using-table-max/.
5 Replies
If with formulas
in P4
=IFERROR(INDEX($H$4:$H$12,AGGREGATE(15,6,1/(COUNTIF($P$3:P3,$H$4:$H$12)=0)*(ROW($H$4:$H$12)-ROW($H$3)),1)),"")in K4
=IF(LARGE($C$4:$C$12*($H$4:$H$12=$P4),1)>1,LARGE($C$4:$C$12*($H$4:$H$12=$P4),1),"")in J4
=IFNA(INDEX($B$4:$B$12,MATCH(1,INDEX(($H$4:$H$12=$P4)*($C$4:$C$12=$K4),0),0)),"")and in the rest of the columns similar to J4. After that drag J4:P4 down.
szub_2 -
If you're willing to try Power Query this can easily be done. This post describes the method https://excelgorilla.com/power-bi/power-query/reach-underlying-rows-using-table-max/.
Thank you for the reply. Would you be willing to provide a screen shot of how you did the grouping? I reviewed the post you provided and I am not getting the same result.
Thanks again.
