Script needed to assign Cluster ID to each PO records based on multiple conditions across columns

[engxiong_lim]

Dear All,

I am working on a test scenario/procedure for split purchase orders such that I would like to create a flag for cluster of POs in the data tables based on multiple conditions as follows:

1. For each PO records, to check the records issued by the same Vendor ID within the 14 days window period

2. Total sum PO value within the 14 days window period by the same Vendor ID should be greater than 15,000 (each PO should be less than 15,000 in this exercise)

3. Assign a Cluster ID to the series of POs in order for the user to be able to deep dive into the details of these POs to determine whether it is a genuine case of split purchase (non-compliance risk of procurement)

Note that column  C-G are the raw data available where I would like to create column A & B based on the above conditions

       

Please find attached excel for data reference as well. Looking forward to a solution. Many thanks!

Best,

Eng Xiong