How to identify the most recent transaction

[szub_2]

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.

[#2 · Accepted solution]

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

[#3]

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

[#4]

@ChrisMendoza,

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.

[#5]

@szub_2 -

Sure thing.

[#6]

@ChrisMendoza,

Thank you.  I worked through this and now I am not sure this approach will work as I have seem to have a lot of duplicates for transactions on the same day for the same serial number in my data, which is what I was trying to avoid. I will definitely keep this approach in mind for other projects.

Thanks for you assistance.