Macro to Pick Out Date of Negative and Max Negative Values by Item for Summary

  • Hi, I'm trying to take a data dump and create a summary via VBA that will give me a summary by Item, Date the Available Qty goes negative, and the maximum amount it goes negative. The data is in this format:



    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]Branch[/TD]
    [TD="width: 133"]Item Number[/TD]
    [TD="width: 64"]Buyer[/TD]
    [TD="width: 64"]QTY Type[/TD]
    [TD="width: 64"]Start
    Date[/TD]
    [TD="width: 64"]Available
    Quantity[/TD]
    [TD="width: 64"]LT[/TD]
    [TD="width: 64"]Stock[/TD]

    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    AAAAAA

    [/td]


    [td]

    123456

    [/td]


    [td]

    AAA

    [/td]


    [td]

    8/24/2018

    [/td]


    [td]

    69.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    AAAAAA

    [/td]


    [td]

    123456

    [/td]


    [td]

    AAA

    [/td]


    [td]

    8/27/2018

    [/td]


    [td]

    -65.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    AAAAAA

    [/td]


    [td]

    123456

    [/td]


    [td]

    AAA

    [/td]


    [td]

    8/28/2018

    [/td]


    [td]

    -65.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    AAAAAA

    [/td]


    [td]

    123456

    [/td]


    [td]

    AAA

    [/td]


    [td]

    8/29/2018

    [/td]


    [td]

    -65.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    AAAAAA

    [/td]


    [td]

    123456

    [/td]


    [td]

    AAA

    [/td]


    [td]

    8/30/2018

    [/td]


    [td]

    -68.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    AAAAAA

    [/td]


    [td]

    123456

    [/td]


    [td]

    AAA

    [/td]


    [td]

    8/31/2018

    [/td]


    [td]

    -300.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    BBBBBB

    [/td]


    [td]

    123456

    [/td]


    [td]

    BBB

    [/td]


    [td]

    8/24/2018

    [/td]


    [td]

    100.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    BBBBBB

    [/td]


    [td]

    123456

    [/td]


    [td]

    BBB

    [/td]


    [td]

    8/27/2018

    [/td]


    [td]

    99.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    BBBBBB

    [/td]


    [td]

    123456

    [/td]


    [td]

    BBB

    [/td]


    [td]

    8/28/2018

    [/td]


    [td]

    -55.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    BBBBBB

    [/td]


    [td]

    123456

    [/td]


    [td]

    BBB

    [/td]


    [td]

    8/29/2018

    [/td]


    [td]

    -67.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    BBBBBB

    [/td]


    [td]

    123456

    [/td]


    [td]

    BBB

    [/td]


    [td]

    8/30/2018

    [/td]


    [td]

    -68.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    BBBBBB

    [/td]


    [td]

    123456

    [/td]


    [td]

    BBB

    [/td]


    [td]

    8/31/2018

    [/td]


    [td]

    -350.00

    [/td]


    [td]

    5

    [/td]


    [td]

    P

    [/td]


    [/tr]


    [/TABLE]


    I'm trying to get to this:



    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]Branch[/TD]
    [TD="width: 64"]Item Number[/TD]
    [TD="width: 70"]Month[/TD]
    [TD="width: 70"]Date Negative[/TD]
    [TD="width: 64"]Max Qty Negative[/TD]

    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    AAAAAA

    [/td]


    [td]

    August

    [/td]


    [TD="align: right"]8/27/2018[/TD]
    [TD="align: right"](300.00)[/TD]

    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    BBBBBB

    [/td]


    [td]

    August

    [/td]


    [TD="align: right"]8/28/2018[/TD]
    [TD="align: right"](350.00)[/TD]

    [/tr]


    [/TABLE]



    So basically, i'm trying to remove all the extra data that's not relevant to my report.


    Any help would be greatly appreciated!!! :yourock:


    Thanks for Reading!!!

  • Hi Jonathan! Sorry for being vague. It's actually an SQL export into excel. It's a query that i run out of another system that returns the Demand for an Item by Day and returns the Item's Quantity Available looking out 2 weeks.

  • I would as well. Unfortunately, I'm using a template in Insight Hubble set up on a scheduler to export that I am unable to modify. I could import the data into MS Access and manipulate it that way but I need to automate this for others who do not have Access at my work. That way I can take vacation and days off and have someone else run the final report from the scheduled export from Hubble. Currently, I have to put these together even on my off days. :(


    I thought I would give the forum a shot to see if VBA might offer a solution.


    I do appreciate your time and help! If it's not a good solution, i'll keep looking.


    Thanks for the quick response! :grin:

  • Ah, okay. I thought you were using a Data Connection to a SQL database, in which case one could use something more than the default SELECT *, particularly if the SQL language supported SQL window functions.


    Should this be this grouped by only Item Number or is it also grouped by Branch and/or Month?

  • For sure Branch and Item number. If the time period on the query was to extend / overlap into the next month (which occasionally it does), it would be great to group those as well.


    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]Branch[/TD]
    [TD="width: 78"]Item Number[/TD]
    [TD="width: 70"]Month[/TD]
    [TD="width: 89"]Date Negative[/TD]
    [TD="width: 109"]Max Qty Negative[/TD]

    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    AAAAAA

    [/td]


    [td]

    August

    [/td]


    [TD="align: right"]8/27/2018[/TD]
    [TD="align: right"](300.00)[/TD]

    [/tr]


    [tr]


    [td]

    OHIO

    [/td]


    [td]

    AAAAAA

    [/td]


    [td]

    September

    [/td]


    [TD="align: right"]9/15/2018[/TD]
    [TD="align: right"](350.00)[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="width: 64"]Branch[/TD]
    [TD="width: 78"]Item Number[/TD]
    [TD="width: 70"]Month[/TD]
    [TD="width: 89"]Date Negative[/TD]
    [TD="width: 109"]Max Qty Negative[/TD]

    [/tr]


    [tr]


    [td]

    INDIANA

    [/td]


    [td]

    AAAAAA

    [/td]


    [td]

    August

    [/td]


    [TD="align: right"]8/27/2018[/TD]
    [TD="align: right"](300.00)[/TD]

    [/tr]


    [tr]


    [td]

    INDIANA

    [/td]


    [td]

    AAAAAA

    [/td]


    [td]

    September

    [/td]


    [TD="align: right"]9/15/2018[/TD]
    [TD="align: right"](350.00)[/TD]

    [/tr]


    [/TABLE]

  • So the first date in each month that has a negative value? Perhaps you could revise your example of the "data dump" to include multiple branches and months and then show what the result set from that should look like.

  • So I thought about this, and I would still use SQL. ;) This groups by Branch, Item Number, and Month, and does not report any items that do not go negative.


    This is hard-coded that the data is at the top left of Sheet1 and the summary report is created on Sheet2.

  • Downloaded and installed ACE driver. Still no luck. No line of code is being highlighted. Just gives that error. I'm using Office Professional 2010 if that helps........

  • That probably means an issue with the connection string, most likely the driver, but it could also be the file name (d). Are you running 64-bit Office by any chance?

  • I googled the error and it can be caused by changing the data being selected and not saving the changes. So try saving the workbook after downloading the data, and then running the subroutine. As you can see, it uses the file (variable d), rather than the (open) workbook. (This is a nice feature because it works without opening workbooks.)

  • What does the top row (header) of your data range look like, exactly? In your above sample, it has "Branch", Item Number", "Buyer", "QTY", "Type", "Start", "Available", "LT", and "Stock." If those aren't the actual headers, then the SELECT statement will need to be changed to reflect what you actually have there. Perhaps it instead should be:

    Code
    sql = "SELECT Branch, [Item Number], MIN([Start Date]) As [Date Negative], '' As [Month], MIN([Available Quantity]) As [Max Qty Negative] " & _
            "FROM [Sheet1$] " & _
            "WHERE [Available Quantity] < 0 " & _
            "GROUP BY Branch, MONTH([Start Date]), [Item Number];"
  • Revised for headers:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!