Segment orderlines based on first - recurring orders

[Makkafon]

Good day,

I have 3 columns: Customer ID, Product ID and Purchase Date.
I want a 4th column for Order type with 2 values: "First Order" and "Recurring order"

Task: Identify the 1st order in the last 12 months for a specific Product ID purchased by the same company, to understand what is the first buy vs subscription.

Been breaking my head trying to sort this, so hoping someone can tell me the best way to do this. Big dataset and each company has a multitude of orders, often for the same product. In short, i need to identify the first order date for each "Company ID" and "Product ID" match and return the first order as "New Order" and subsequent orders of the same product under the same company as "Recurring".

Cheers!

 

[kevin9999]

Something like this perhaps?

Book1
	A	B	C	D
1	Customer ID	Product ID	Purchase date	Recurring?
2	aaa	PID 1	1/01/2023	First Order
3	aaa	PID 2	2/01/2023	First Order
4	aaa	PID 3	3/01/2023	First Order
5	aaa	PID 1	4/01/2023	Recurring Order
6	aaa	PID 1	5/01/2023	Recurring Order
7	aaa	PID 1	6/01/2023	Recurring Order
8	aaa	PID 2	7/01/2023	Recurring Order
9	aaa	PID 3	8/01/2023	Recurring Order
10	bbb	PID 1	9/01/2023	First Order
11	bbb	PID 2	10/01/2023	First Order
12	bbb	PID 3	11/01/2023	First Order
13	bbb	PID 1	12/01/2023	Recurring Order
14	bbb	PID 1	13/01/2023	Recurring Order
15	bbb	PID 1	14/01/2023	Recurring Order
16	bbb	PID 2	15/01/2023	Recurring Order
17	bbb	PID 3	16/01/2023	Recurring Order
18	ccc	PID 1	17/01/2023	First Order
19	ccc	PID 2	18/01/2023	First Order
20	ccc	PID 3	19/01/2023	First Order
21	ccc	PID 1	20/01/2023	Recurring Order
22	ccc	PID 1	21/01/2023	Recurring Order
23	ccc	PID 1	22/01/2023	Recurring Order
24	ccc	PID 2	23/01/2023	Recurring Order
25	ccc	PID 3	24/01/2023	Recurring Order
Sheet1

Cell Formulas
Range	Formula
D2:D25	D2	=IF(C2=MIN(IF((A:A=A2)*(B:B=B2),C:C)),"First Order","Recurring Order")