I need code for last purchase or transaction Date for my inventory system

I work for a supermarket and my job is to manage inventory of the Supermarket. I just want to add some more information in my inventory data I guess whoever knows the coding will be able to help me. Let me give you guys my problem description In my inventory data column are as itemcode, purchasedate, quantity, amount and I have another sheet in which I just have item_code and in that I want to add last purchase date, no. of purchase and total purchase. If you know code for this or any related please post it here, Thank you.

I have done a project for inventory, but it’s hard to tell you without knowing the code for your inventory or sample book. It will be better for me if you post your sample book and others will also easily understand that what changes or code they have to add. In my project I used Max function to get the last transaction date for products in medical, so I think at least you should try Max function to find the last date. And do tell me if it worked for you or not.

Thanks mate for such a quick reply, I never thought I will get reply so fast. I tried your solution but it didn’t worked for me, May be coding concept behind the your medical project and mine inventory system is different. In my inventory system I have to use Max function & sumproduct in macro as

Code:

---

worksheetfunction.SumProduct(.Max((ws1.range("B3:B", 100)=A3)*'ws2.range("I3:I",100)))"

---

here worksheets are defined as ws1 and ws2.
Sorry but your code is not executing, please help is there any other problem in code

Dude still you haven’t uploaded any sample book so it’s not easy to understand what is the problem and where is the code mistake, as said before please upload your sample book so it will more convenient for me and for others to understand your problem easily. And by looking at your code some things are not clear like what is “.MAX”, according to me it should be

Code:

---

worksheetfunction.Max or it  can be like this with worksheetfunction
l = .SumProduct(.Max((ws1.range("B3:B", 100)=A3)*'ws2.range("I3:I",100)))"

end with

---

and one more thing is not clear why there is ‘ before ws2.range ?

Ok buddy here’s the Macro code please have a look

Code:

---

Sub report()
Dim ws1 As Worksheet
Dim wsR As Worksheet
Dim LastRow, j, t  As Long
Dim i As Integer

On Error Resume Next
Set ws1 = Worksheets("Trns")
Set wsR = Worksheets("Report")
wsR.Select
 
 LastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
 
 ws1.Range("A2:A" & LastRow).AdvancedFilter Action:=xlFilterCopy, _
 CopyToRange:=wsR.Range("A1"), Unique:=True
 
 j = wsR.Range("A" & Rows.Count).End(xlUp).Row
 For i = 2 To j
   
    With WorksheetFunction
            t = Cells(i, 1).Value
            Cells(i, 2).Value = .CountIf(ws1.Range("A:A"), Cells(i, 1))
            Cells(i, 3).Value = .SumIf(ws1.Range("A:A"), Cells(i, 1), ws1.Range("C:C"))
            Cells(i, 4).Value = .SumProduct(.Max((ws1.Range("a3:a100") = t) * ws1.Range("D3:d100")))
           
    End With
 Next i
 
 End Sub

---

Hope many things will be clear to you. And I sent you sample book. And there is no ‘ before ws2.range it’s just a typing mistake.