Last Transaction

[Harini1100]

Hello Team,

Need help to find the recent transaction date and sale count from the input value. Please advise,

 

[hagia_sofia]

In 365:

=TAKE(SORT(FILTER(B3:C7,A3:A7=E3)),-1)

 

[abdelfattah]

Hi Harini,
if you like vba based on your OP

Sub Copylast_row()
Dim strUserResponse As String
Dim lastrow As Long
Dim lr As Long
Dim lr1 As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row 
lr = Cells(Rows.Count, 2).End(xlUp).Row
lr1 = Cells(Rows.Count, 3).End(xlUp).Row
ActiveSheet.Range("E3").Value = ActiveSheet.Range("A" & ActiveSheet.Range("A" & Rows.Count).End(3).Row).Value
ActiveSheet.Range("F3").Value = ActiveSheet.Range("B" & ActiveSheet.Range("B" & Rows.Count).End(3).Row).Value
ActiveSheet.Range("G3").Value = ActiveSheet.Range("C" & ActiveSheet.Range("C" & Rows.Count).End(3).Row).Value
End Sub

before

TB.xlsm
	A	B	C	D	E	F	G
1
2	UNIT	DATE	SALE COUNT		UNIT	DATE	SALE COUNT
3	A	5/1/2024	123
4	A	5/20/2024	1100
5	A	6/5/2024	901
6	A	6/15/2024	552
7	A	6/20/2024	1800
SH

after

TB.xlsm
	A	B	C	D	E	F	G
1
2	UNIT	DATE	SALE COUNT		UNIT	DATE	SALE COUNT
3	A	5/1/2024	123		A	20/6/2024	1800
4	A	5/20/2024	1100
5	A	6/5/2024	901
6	A	6/15/2024	552
7	A	6/20/2024	1800
SH

and don't merge cells in row 1 avoid error in the code , next time use XL2BB tool to show where start data from .

 

[Harini1100]

Thanks for Awesome response, Unfortunately I need formula only for 365,

@hagia_sofia - =TAKE(SORT(FILTER(B3:C7,A3:A7=E3)),-1) This is working fine, could you please do little modification for the below request, This input is the original format from one of my PATHETIC software. I shown the example of only 2 invoice, but i'm dealing with n number of invoices on daily basis. Kindly help.

 

[Cubist]

This seems to work. Try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Invoice#	Date	Product	Op Quantity	Closing Quantity	Msg. Date	Message		Invoice#	Date	Product	Op Quantity	Closing Quantity	Msg. Date	Message
2	1234	12/1/23	Rubber	100	50	-	-		1234	12/1/23	Rubber	100	50	2/16/24	DSL
3						1/5/24	ABC		1234	12/1/23	Clamp	500	633	2/20/24	OKO
4						1/12/24	DEF		1895	1/9/24	Shaft	22	8	3/18/24	QWE
5						1/19/24	SDAF
6						1/26/24	WRW
7						2/2/24	GHF
8						2/9/24	GERT3
9						2/16/24	DSL
10	1234	12/1/23	Clamp	500	633	-	-
11						1/9/24	DCDF
12						1/16/24	UIO
13						1/23/24	DSFJL
14						1/30/24	DCLDM
15						2/6/24	R234
16						2/13/24	ASCDA
17						2/20/24	OKO
18	1895	1/9/24	Shaft	22	8	-	-
19						2/5/24	MKN
20						2/12/24	SDF
21						2/19/24	CIJ
22						2/26/24	WE
23						3/4/24	DCO
24						3/11/24	MKD
25						3/18/24	QWE
Sheet4

Cell Formulas
Range	Formula
I2:M4	I2	=FILTER(A2:E25,A2:A25<>"")
N2:O4	N2	=LET(a,$A$2:$A$25,b,B$2:$B$25,c,$C$2:$C$25, d,IF(a="",SEQUENCE(ROWS(a))), x,XMATCH(1,(a=I2)*(b=J2)*(c=K2)), CHOOSEROWS($F$2:$G$25,IFERROR(x-1+XMATCH(FALSE,DROP(d,x)),ROWS(a))))
Dynamic array formulas.

 

[Cubist]

Simplified a little bit more.

Book1
	I	J	K	L	M	N	O
1	Invoice#	Date	Product	Op Quantity	Closing Quantity	Msg. Date	Message
2	1234	12/1/23	Rubber	100	50	2/16/24	DSL
3	1234	12/1/23	Clamp	500	633	2/20/24	OKO
4	1895	1/9/24	Shaft	22	8	3/18/24	QWE
Sheet4

Cell Formulas
Range	Formula
I2:M4	I2	=FILTER(A2:E25,A2:A25<>"")
N2:O4	N2	=LET(a,$A$2:$A$25,b,B$2:$B$25,c,$C$2:$C$25,f,$F$2:$G$25, x,XMATCH(1,(a=I2)*(b=J2)*(c=K2)), CHOOSEROWS(f,IFERROR(x-1+XMATCH(FALSE,DROP(a="",x)),ROWS(a))))
Dynamic array formulas.

 

[Cubist]

One more option as a single spill range.

Book1
	I	J	K	L	M	N	O
1	Invoice#	Date	Product	Op Quantity	Closing Quantity	Msg. Date	Message
2	1234	12/1/23	Rubber	100	50	2/16/24	DSL
3	1234	12/1/23	Clamp	500	633	2/20/24	OKO
4	1895	1/9/24	Shaft	22	8	3/18/24	QWE
Sheet4

Cell Formulas
Range	Formula
I2:O4	I2	=LET( a,A2:A25<>"",b,A2:E25,c,F2:G25,r,ROWS(a), HSTACK(FILTER(b,a),CHOOSEROWS(c,VSTACK(DROP(TOCOL(IFS(a,SEQUENCE(r)),2),1)-1,r))))
Dynamic array formulas.