Macro/Automating Edits and Calculations for Large Report (40K rows)

[mr_steveb]

Hello, forum!

We have an anachronistic Warehouse Management System that does not do everything we need it to. I have a large exported CSV with over 40K rows that I have to "massage" in order to get it properly formatted and to do some of the desired analyses. Such efforts can take a while to do, and I'd like to try and find a way to automate this to make it go faster.

My initial inclination was to do a macro, but when I tried to do so, it shut itself down saying that it was too many rows. My main goal is to automate the process of implementing these edits, but I'm including details and current measures below for those interested in digging further. But the TL;DR is: Can I / how do I automate edits and formula entries for a large file where a macro seems to not work?

===

To give an overview of the kinds of edits and analyses I'm doing:

Excerpt of file: MrExcel Example File (I think I fixed the errors caused by sharing it to Google Drive) or Dropbox (Dropbox)

• Formatting some of the headings due to the Fixed Width import cutting them off
  • e.g. "REORDE" and "RMAX" are reformatted to be "REORDER" and "MAX"
• Adding columns, labeling them, then populating them with additional info
  • e.g. Vendor 011 has code 0110 - current process is using PowerQuery to "Fill Down" the Vendor info, extract the number, then running a formula to do that number + 0 ("=[cell number]&0")
  • e.g. Item P22 .79 OZ PERSON DEFENS has SKU of "P22". Using a formula to extract everything before a space to isolate the SKU, then conjoining the Vendor Code + the SKU (e.g. 0110P2)
• Consolidating quantities of items that are flagged as having "OLD" or "NEW" variations so that when we are doing orders, we have a correct sense of what we have on hand
  
  • e.g. Item "HC14PKUS" (3 in stock) was replaced by "HCNBCF01" (19 in stock) For the sake of ordering, we would want HC14PKUS to report 22 total items on hand since they're functionally identical and we wouldn't want to order more of the "old item."
  • Likewise, "P22JOC02" replaced "P22JOCUS", so we would want to combine the QTY OH for both of these.
  • The current approach is basically to create new columns to the right that re-display these quantities, then combine them to create a new "Updated Qty" value
• Creating and running formulas from original or extrapolated values
  
  • We put together orders for items if what we need to order crosses a certain $ threshold in order to get free shipping.
  • We usually order items if it falls below a certain threshold (Reorder Point), but we may also choose to order items if they're getting below a "Max On Hand" point and we decide we should order sooner to maintain inventory or if shipments may take longer to arrive
  • Items are generally sold in packs, cases, etc. rather than individual quantities, so we have to order sets
    • e.g. we have 4 OH and the Reorder Point is 8. The item comes in cases of 12, so we'd have to order 12 and end up with 16 OH
  • If items are "Back Ordered," they need to be added to "QTY OH" since we anticipate they would be coming in and adding to the inventory.
    • So in the previous example of 4 OH and Reorder Point of 8, if we had 12 back-ordered, we would regard as having 16 OH and would not order more
  • Once we account for QTY OH and how much we would order, we would multiply this quantity by the cost per item.
  • The current approach is basically
    • add "Updated Qty" + "Back-Ordered", then subtract this by "Reorder Point" or "Max OH" to create two separate quantities to replenish based on two different standards.
    • divide each value by "Case Quantities", then =ROUNDUP to account for the fact that we need to order full cases to get to the desired number
      • e.g. if we have 2 of something, want to get to 5, and they come in packs of 12, the .25 cases we'd need rounds up to 1 full case and would get us to 14 on-hand
    • multiply this integer value by Case Quantity, then that value by price to know how much it would cost
    • .25 cases becomes 1 case; 1 case is 12 items, each item is $5.50, so ordering a case would be $66
  • From there, I do a =SUMIF based on vendor codes to see how much an order would be for a particular vendor

I hope this all makes sense and appreciate anyone making it through all of that.

-Steve

 

[mr_steveb]

Hello,

Checking in on this again to re-inquire on the TL;DR point: What would be the recommended ways to automate revisions to large datasets if the Macro function is shutting down due to being too lengthy?

 

[kevin9999]

Hi Steve,
Could you elaborate on "shutting down due to being too lengthy". I'd like to see a small sample of your CSV data (not all 40k rows) as well as your desired end result, and also your current macro.

 

[pjmorris]

Based on my experience I'd be looking at using Powerquery. Its well worth the investment to learn and the GUI is pretty intuitive.

 

[mr_steveb]

Hi Steve,
Could you elaborate on "shutting down due to being too lengthy". I'd like to see a small sample of your CSV data (not all 40k rows) as well as your desired end result, and also your current macro.

Hi Kevin,

There should be a Google Sheet and a Dropbox link in my post that should have a before and after sample.

Thank you!
- Steve

 

[mr_steveb]

Based on my experience I'd be looking at using Powerquery. Its well worth the investment to learn and the GUI is pretty intuitive.

Hi Peter,

I do currently use Power Query to perform operations; the "Fill Down" option has been instrumental in speeding up how I edit these reports. The further issue is that some of the formatting changes I make are time-consuming, even if they're straightforward, and I wanted to see if there were faster ways to get to the same outcomes

 

[kevin9999]

Hi Kevin,

There should be a Google Sheet and a Dropbox link in my post that should have a before and after sample.

Thank you!
- Steve

Thanks Steve,

I was hoping that the data in the incoming csv might have been contiguous, in which case I had plenty of suggestions (irrespective of the number of rows) but it looks like it isn't. It seems like you provided an example using one product code and I assume there are many with a similar layout? My gut feeling is that a PowerQuery solution could be the answer, and that's not my thing. Hopefully the experts in that area will see your thread and provide a solution. Best wishes & good luck!