Forum Discussion

szub_2's avatar
szub_2
Copper Contributor
Jan 22, 2020
Solved

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 transa...
excel
  • ChrisMendoza's avatar
    ChrisMendoza
    Jan 22, 2020

    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/.

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 22, 2020

szub_2 

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.

Resources