Help needed- Analysis of splitting of orders

[anish.ms]

Dear Experts,

I'm a little confused here and request your help with the code to test the below-:help

From the sample file, I needed a list of items in column D ordered from the same supplier in column C within 7 days (order dates in column B) and the total amount of all orders in those 7 days exceeding 50,000 (amount in column E).
Basically, for every item code, it has to check the previous 7 days' orders from the same supplier and the total amount of the purchase. If the total amount exceeds 50,000 then all those orders have to be copied to a new sheet. There can be multiple orders.

thanks in advance

[arnelgp]

what do you mean "within 7 days"?

[anish.ms]

Thanks, arnelgp for the response
Let's say if there is an order today i.e., 17-Oct for an item ABC to the supplier XYZ, I need the list of all orders between 10-Oct to 17-Oct for the same item to the same supplier if the total amount of all such orders exceeds 50,000.

[arnelgp]

if you will filter the [po date], you will find that there is No record for October, 2022.
so no record will qualify for your first condition (on date).

[anish.ms]

I said October as an example. The attached sample data is for the period Jan 2022 to Sep 2022

[p45cal]

for every item code, it has to check the previous 7 days' orders
Previous 7 days from what date?

From each row in the data
From today's date?
From the last date anything was ordered from each vendor
Something else?

[anish.ms]

Thanks p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal) for your response

previous 7 days for each row in the data

I have sorted the data in the order of Item, VEN #, and PO Date and tried using a formula but was not sure of taking the sum for the previous 7 days against each row. And the formula for large data with 200,000+ rows will make excel stuck/not respond.

[p45cal]

previous 7 days for each row in the dataWell, it would be the most difficult ask!
While I feel sure a formula in Office365 would be possible it would be hard work and yes, it would be resource hungry.
In the attached a Power Query solution (not streamlined) at cell I1. (You can cut and paste the table to another sheet.) There are some zero Amt rows in the result because they were included with the seven day period for a vendor/item combination which did exceed 50000; easy enough to filter out if you don't want to see them.

There's a named range threshold at cell H2 (highlighted yellow) which allows you to set the threshold. Change this then refresh the query.

Edit:
While testing the threshold I noticed that there are duplicate rows in your source data; one example is rows 40459:40461. Is this right?

[anish.ms]

Thanks p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal) for your time and PQ solution.
I will check the duplicates that you have highlighted.
Great that you kept the threshold as an option to change. Similarly, can you tell me where I can change the period 7 days to a different period? and if it's a single instance exceeding 50,000 can be excluded from the list

[p45cal]

Attached

[arnelgp]

not very fast but will do what you need.
run the code on Module1 and the result will be
written in Sheet2.

[anish.ms]

Thanks p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal)
Let me try to learn how it is done

[anish.ms]

Thanks arnelgp
Thanks for your time and help
I'm getting a run time error Method 'Open' of object '_Connection' failed


conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& ThisWorkbook.FullName _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

[arnelgp]

what version of office do you use?
the connection string i provided is for office 2007 and newer.

EDIT.

i see that your excel has .xlsb extension which means it is 2007-or better
version of office.
i don't know why you are getting the error.
did you enable the "yellow" macro warning?

[anish.ms]

I'm using office 365
Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20704) 64-bit
Do I need any special VBA project reference?

[arnelgp]

i don't know if it is relevant but Ace.Ole, i think belongs to Access library? try googling it.

[arnelgp]

If you have Access, you can import your sheet as Table (see sheet3).
add index on the the table and it will be very fast.
run code in Module1. the result will be outputted to OUTPUT table.
you can then Export OUTPUT table to an Excel file.

[p45cal]

Thanks arnelgp
Thanks for your time and help
I'm getting a run time error Method 'Open' of object '_Connection' failed


conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& ThisWorkbook.FullName _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

anish, I have Office 365 64-bit too and receive no such error.

However, fyi arnelgp, I'm in the UK and the dates are not filtering properly;
At one stage in the code I get the sql string:

SELECT * FROM [Sheet1$A3:E20001] WHERE [VEN #] = 10050 AND [ITEM] = 'CN.UR.100037' AND [PO Date] >= #03/03/2022# AND [PO Date] <= #10/03/2022# ORDER BY [PO Date];

which returns a record set:

400001936 03/03/2022 10050 CN.UR.100037 2,200.00
100029882 06/04/2022 10050 CN.UR.100037 3,300.00
100030124 08/04/2022 10050 CN.UR.100037 7,700.00
100030124 08/04/2022 10050 CN.UR.100037 7,700.00
100030124 08/04/2022 10050 CN.UR.100037 7,700.00
100030124 08/04/2022 10050 CN.UR.100037 7,700.00
100031909 03/05/2022 10050 CN.UR.100037 2,200.00
100036101 30/06/2022 10050 CN.UR.100037 11,000.00
100037812 28/07/2022 10050 CN.UR.100037 4,400.00
but if I manually change #10/03/2022# to #03/10/2022# in that statement I get:

400001936 03/03/2022 10050 CN.UR.100037 2,200.00

[anish.ms]

I really don't know why I'm getting the error

[arnelgp]

However, fyi arnelgp, I'm in the UK and the dates are not filtering properly;
you can use ISO-date format (yyyy-mm-dd), yet when i used it in the code (xlsb) i get Same result.



...
...
sql = "SELECT Sum([Amt]) As SumAmt FROM [Sheet1$" & s & "] WHERE [VEN #] = " & .Fields(0) & " AND [ITEM] = '" & .Fields(1) & "' " & _
" AND [PO Date] >= #" & Format$(.Fields(2), "yyyy-mm-dd") & "# " & _
" AND [PO Date] <= #" & Format$(.Fields(2) + Sheet1.Range("Days"), "yyyy-mm-dd") & "# GROUP BY [VEN #], [ITEM];"

rs2.Open sql, conn, 3, 1

If Not rs2.EOF Then
If Val(rs2("SumAmt") & "") <= Sheet1.Range("Limit") Then
GoTo loop_next_record
End If
Else
GoTo loop_next_record

End If

rs2.Close

sql = "SELECT * FROM [Sheet1$" & s & "] WHERE [VEN #] = " & .Fields(0) & " AND [ITEM] = '" & .Fields(1) & "' " & _
" AND [PO Date] >= #" & Format$(.Fields(2), "yyyy-mm-dd") & "# " & _
" AND [PO Date] <= #" & Format$(.Fields(2) + Sheet1.Range("Days"), "yyyy-mm-dd") & "# ORDER BY [PO Date];"
...
...

[p45cal]

Yes, the Format$(.Fields(2), "yyyy-mm-dd") worked. Thanks.
Incidentally, this also worked " AND [PO Date] >= " & CLng(.Fields(2)) &
Then I had the problem of finding why this ended up with a much shorter list than the Power Query of the same data. After a lot of messing with the SQL (I'm no conoisseur of SQL) I turned my attention to the first sql in the macro where (I think) you take only a single minimum date from each vendor/item combination and search only 7 or 8 days after that date for sums greater than 50000, whereas there are possibly other clusters of 7 days later on fulfilling the same criterion. So I tried changing the first sql string from:

rs.Open "SELECT [VEN #], [ITEM], MIN([PO Date]) AS DTE FROM [Sheet1$" & s & "] WHERE Not ([Po Date] Is Null) GROUP BY [VEN #], [ITEM];", conn
to:

rs.Open "SELECT DISTINCT [VEN #], [ITEM], [PO Date] FROM [Sheet1$" & s & "] WHERE Not ([Po Date] Is Null);", conn
(realising that such a recordset is almost a long as the source data) and it produced the same results as Power Query (after depuplicating both sets of results).

[anish.ms]

Hi p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal)

Could you please check the below?
If I keep the threshold at 300,000 and days to 10, I'm getting the item PH.CS.100094 having the amount of 69688.32
Thanks,

[p45cal]

If I keep the threshold at 300,000 and days to 10, I'm getting the item PH.CS.100094 having the amount of 69688.32
With the file I attached in msg# 10, vbaExpress70318Samplev2.xlsb, I get:
30299

which doesn't include that record. Have you changed something?

I do get it if I bring the threshold down to 200,000.

This record occurs three times in the source data; same details across ALL the columns. This would be forbidden in a normal database because it can only be one order.
30298

I pointed this out in msg#8
While testing the threshold I noticed that there are duplicate rows in your source data; one example is rows 40459:40461. Is this right?

The query takes account of the 3 similar entries and sums them to around 210k so includes them in the 200,000 threshold result. A final step removes duplicates, a step which with a real database (no duplicate records) wouldn't be a problem.

If you think it's legitimate to have duplicate records in your database I think I could get the final table to show the duplicates if you wanted. Maybe you've only shown a subset of columns from your real database?, in which case, again the query could be tweaked to show the three rows.

Edit:
The picture below shows the same query without removing the duplicates which shows there are many such duplicate rows (highlighted). The column headed Index shows the line number in the database (which is 1 different from the row number on the sheet).
30300

[anish.ms]

Hi p45cal
Sorry for the confusion. I add more data now. I think my sample was limited due to the file size.
Yes, I wanted even if there are duplicates in the data.
I also wanted to check one more parameter, which is business unit. I have combined it with the vendor code for the time being
Can you replace column1 with the business unit and add that into the parameter to be changed along with Item, VEN #, Amt, and PO Date?

Thanks

[p45cal]

You'd better supply some more data.
How do you get the data into Excel? Has it come from somewhere alse, such as a csv file or .txt file? If so supply that instead. Power Query is built to bring that sort of data in and it will keep te workbook size small.
If you leave the file in the same form as you get the data it will be easier for you later when you get updated data.
If the file is too big to attach here, upload it to a file sharing site, (oneDrive, SkyDrive, Google Drive, Box.net, whatever) and link to it here.
Provide clear details of what you want it to do.

[anish.ms]

Thanks p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal)!


Given below the link to the original file with the required information. This report is exported directly into excel from Oracle.
https://drive.google.com/file/d/11EEXvZvFOtqyKqCCWRBH0vxVG_uoqcmp/view?usp=share_link

I have highlighted the columns which need to be considered to identify the splitting of PO. I need to keep the threshold and days dynamic and refresh the query to get the results like you did for the previous version. Threshold and days can be kept anywhere in the first 2 rows. Let's say the threshold is 200,000 and the days are 10, I need the list of purchase orders issued for the same item (N) to the same vendor (F) from the same organisation (A) with each line amount (V) less than the threshold, but the aggregate of nearby 10 days (D) is crossing the threshold.

This is to identify if any purchase orders are split to bypass the approval workflow. Let's say the orders above 200,000 require a higher approval and to bypass the same the purchase orders are spitted into multiple orders. For example, an order of 250,000 is split into 2 orders of 125,000 each OR 5 orders of 50,000 each on the same day or let's say within 10 days.
Hope I was able to explain in detail.

Thanks for your help.

[p45cal]

See if this does it:
https://app.box.com/s/0ebug88ahwe3rsrcgiqlm32ixzh8yav2

[anish.ms]

Thanks a Ton p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal)
Can you add PO number as well to the list?

[anish.ms]

Hi p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal)
I could manage to add the PO number
Thanks