Calculate Number of Days Between Orders of Same Item

[mcmuney]

Hello, the below is a scenario for ordering items and I'm looking to calculate the number of days between each for each item. Once I have the days, I can't seem to figure out how to calculate this.

Any help would be greatly appreciated. Red font would be the expected results.

Thanks!

Order Date (colA)	Item (colB)	# Ordered (ColC)	Days Between Orders of Same Item
10/1/23	A1	5	n/a since it's the first item
10/6/23	A1	2	5 days (A3-A2)
10/7/23	B1	3	n/a since it's a new item
10/15/23	A1	1	9 days (A5-A3)
10/25/23	A1	4	10 days (A6-A5)
10/26/23	C1	2	n/a since it's a new item
10/27/23	B1	1	20 days (A8-A4)

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E
1	Order Date (colA)	Item (colB)	# Ordered (ColC)	Days Between Orders of Same Item
2	10/1/2023	A1	5		n/a since it's the first item
3	10/6/2023	A1	2	5	5 days (A3-A2)
4	10/7/2023	B1	3		n/a since it's a new item
5	10/15/2023	A1	1	9	9 days (A5-A3)
6	10/25/2023	A1	4	10	10 days (A6-A5)
7	10/26/2023	C1	2		n/a since it's a new item
8	10/27/2023	B1	1	20	20 days (A8-A4)
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=IFERROR(A2-LOOKUP(2,1/($B$1:B1=B2),$A$1:A1),"")

 

[mcmuney]

Amazing, thank you so much!

 

[Peter_SSs]

With Excel 365 you could also use this

=IFNA(A2-XLOOKUP(B2,B$1:B1,A$1:A1,,,-1),"")

 

[mcmuney]

Thank you Peter! Both formulas work, but I can't determine if one is more efficient than the other

 

[Peter_SSs]

You're welcome.

I can't determine if one is more efficient than the other

If the data is small you wouldn't notice the difference. However, as the data size grows, XLOOKUP gets relatively faster, I think due mainly to the slowness of all the division calculations in the LOOKUP formula.
I just did a test with 5,000 rows of data. To calculate the 5,000 results ..
XLOOKUP: 0.066 seconds
LOOKUP: 0.98 seconds

So a time difference factor of about 15x for this size data (on my machine)