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