BrutalDawg
New Member
- Joined
- Jun 10, 2015
- Messages
- 41
I have spent the last few hours trying to find something similar to the issue I am having and currently have smoke coming out of my ears!
I have two sheets, one with orders the second with sales.
I need help automatically subtracting from orders based on sales.
I am trying to develop a script that would run through every sale and if location and item are exactly the same, subtract quantity sold from the quantity ordered to give a "new due" quantity.
There will be sales with no order which is a non issue, but would be nice if those results could go to a different tab. Below is a summary of what is required in n00b terms.
<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet1</code> is sold, <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet2</code> is order.
If <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet1</code> C2 & F2 are same as any row in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet2</code> (A2,D2 are correlating fields) subtract <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet1</code>G2, from <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet2</code> F2.
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
On the results tab everything highlighted in yellow is what would have changed. Items not highlighted in yellow there was no sale. The green and red formatting was for reference and is not required if to difficult. (would be ideal) column H, is illustrating, if this is even possible, once the order quantity reaches 0, for the script to keep searching sheet for next order with same criteria and subtract remain quantity. Anything will help, I am currently doing this manually over the weekend and generally 150+ sales per week and is very time consuming.
In simple terms, I am trying to have a script go through each sale row and subtract quantity from the order sheet if the plant and part number are the same. I will sort the order sheet based on when they are do so it removes from correct order.
Thanks for any help!
I have two sheets, one with orders the second with sales.
I need help automatically subtracting from orders based on sales.
I am trying to develop a script that would run through every sale and if location and item are exactly the same, subtract quantity sold from the quantity ordered to give a "new due" quantity.
There will be sales with no order which is a non issue, but would be nice if those results could go to a different tab. Below is a summary of what is required in n00b terms.
<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet1</code> is sold, <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet2</code> is order.
If <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet1</code> C2 & F2 are same as any row in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet2</code> (A2,D2 are correlating fields) subtract <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet1</code>G2, from <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet2</code> F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Type | Date | Ship To Address 1 | Ship To Address 2 | Ship Zip | Item | Qty |
| 2 | Invoice | 1/25/2017 | Ohio | 5698 | 60 | ||
| 3 | Invoice | 1/25/2017 | Oklahoma | 5693 | 50 | ||
| 4 | Invoice | 1/24/2017 | TEXAS | 5701 | 220 | ||
| 5 | Invoice | 1/25/2017 | TEXAS | 5699 | 112 | ||
| 6 | Invoice | 1/26/2017 | Florida | 5729 | 20 | ||
| 7 | Invoice | 1/25/2017 | Oklahoma | 5693 | 205 | ||
| 8 | Invoice | 1/25/2017 | Mexico | 5695 | 165 | ||
| 9 | Invoice | 1/25/2017 | Ohio | 5698 | 45 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sold
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Plant | IssueDate | PurchaseOrderNumber | PartNumber | Timing | Quantity | When |
| 2 | Texas | 1/22/2017 | AI437B | D1843 | Immediate:Discrete | 13 | 1/22/2017 |
| 3 | Oklahoma | 1/22/2017 | AI589A | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 28 | 1/23/2017 |
| 4 | Texas | 1/22/2017 | AI437B | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 9 | 1/23/2017 |
| 5 | Texas | 1/22/2017 | AI437B | D1843 | Firm:Weekly Bucket (Monday through Sunday) | 174 | 1/23/2017 |
| 6 | Oklahoma | 1/22/2017 | AI589A | D1882 | Firm:Weekly Bucket (Monday through Sunday) | 3 | 1/30/2017 |
| 7 | Oklahoma | 1/22/2017 | AI589A | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 205 | 1/30/2017 |
| 8 | Texas | 1/22/2017 | AI437B | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 3 | 1/30/2017 |
| 9 | Texas | 1/22/2017 | AI437B | D1843 | Firm:Weekly Bucket (Monday through Sunday) | 110 | 1/30/2017 |
| 10 | OHIO | 1/22/2017 | AI589A | D1874 | Firm:Weekly Bucket (Monday through Sunday) | 60 | 1/30/2017 |
| 11 | OHIO | 1/22/2017 | AI589A | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 42 | 1/30/2017 |
| 12 | Mexico | 1/22/2017 | AI437B | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 1 | 1/30/2017 |
| 13 | Mexico | 1/22/2017 | AI589A | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 164 | 1/30/2017 |
| 14 | Oklahoma | 1/22/2017 | AI589A | D1882 | Firm:Weekly Bucket (Monday through Sunday) | 2 | 2/6/2017 |
| 15 | Oklahoma | 1/22/2017 | AI589A | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 204 | 2/6/2017 |
| 16 | Texas | 1/22/2017 | AI437B | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 6 | 2/6/2017 |
| 17 | Texas | 1/22/2017 | AI437B | D1843 | Firm:Weekly Bucket (Monday through Sunday) | 188 | 2/6/2017 |
| 18 | OHIO | 1/22/2017 | AI589A | D1874 | Firm:Weekly Bucket (Monday through Sunday) | 60 | 2/6/2017 |
| 19 | OHIO | 1/22/2017 | AI589A | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 299 | 2/6/2017 |
| 20 | Mexico | 1/22/2017 | AI437B | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 3 | 2/6/2017 |
| 21 | Mexico | 1/22/2017 | AI589A | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 197 | 2/6/2017 |
| 22 | Oklahoma | 1/22/2017 | AI589A | D1882 | Firm:Weekly Bucket (Monday through Sunday) | 10 | 2/13/2017 |
| 23 | Oklahoma | 1/22/2017 | AI589A | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 166 | 2/13/2017 |
| 24 | Texas | 1/22/2017 | AI437B | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 4 | 2/13/2017 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Order
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Plant | IssueDate | PurchaseOrderNumber | PartNumber | Timing | Quantity | When | |
| 2 | TEXAS | 1/22/2017 | D1843 | Immediate:Discrete | -299 | 1/22/2017 | 0 | |
| 3 | Oklahoma | 1/22/2017 | D1852 | Firm:Weekly Bucket (Monday through Sunday) | -177 | 1/23/2017 | 0 | |
| 4 | TEXAS | 1/22/2017 | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 9 | 1/23/2017 | ||
| 5 | TEXAS | 1/22/2017 | D1843 | Firm:Weekly Bucket (Monday through Sunday) | 174 | 1/23/2017 | -45 | |
| 6 | Oklahoma | 1/22/2017 | D1882 | Firm:Weekly Bucket (Monday through Sunday) | -47 | 1/30/2017 | 0 | |
| 7 | Oklahoma | 1/22/2017 | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 205 | 1/30/2017 | 28 | |
| 8 | TEXAS | 1/22/2017 | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 3 | 1/30/2017 | ||
| 9 | TEXAS | 1/22/2017 | D1843 | Firm:Weekly Bucket (Monday through Sunday) | 110 | 1/30/2017 | ||
| 10 | OHIO | 1/22/2017 | D1874 | Firm:Weekly Bucket (Monday through Sunday) | 0 | 1/30/2017 | ||
| 11 | OHIO | 1/22/2017 | D1852 | Firm:Weekly Bucket (Monday through Sunday) | -3 | 1/30/2017 | 0 | |
| 12 | Mexico | 1/22/2017 | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 1 | 1/30/2017 | ||
| 13 | Mexico | 1/22/2017 | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 164 | 1/30/2017 | ||
| 14 | Oklahoma | 1/22/2017 | D1882 | Firm:Weekly Bucket (Monday through Sunday) | 2 | 2/6/2017 | ||
| 15 | Oklahoma | 1/22/2017 | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 204 | 2/6/2017 | ||
| 16 | TEXAS | 1/22/2017 | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 6 | 2/6/2017 | ||
| 17 | TEXAS | 1/22/2017 | D1843 | Firm:Weekly Bucket (Monday through Sunday) | 188 | 2/6/2017 | ||
| 18 | OHIO | 1/22/2017 | D1874 | Firm:Weekly Bucket (Monday through Sunday) | 60 | 2/6/2017 | ||
| 19 | OHIO | 1/22/2017 | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 299 | 2/6/2017 | 296 | |
| 20 | Mexico | 1/22/2017 | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 3 | 2/6/2017 | ||
| 21 | Mexico | 1/22/2017 | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 32 | 2/6/2017 | ||
| 22 | Oklahoma | 1/22/2017 | D1882 | Firm:Weekly Bucket (Monday through Sunday) | 10 | 2/13/2017 | (-37) | |
| 23 | Oklahoma | 1/22/2017 | D1852 | Firm:Weekly Bucket (Monday through Sunday) | 166 | 2/13/2017 | ||
| 24 | TEXAS | 1/22/2017 | D1876 | Firm:Weekly Bucket (Monday through Sunday) | 4 | 2/13/2017 | ||
| 25 | TEXAS | 1/22/2017 | D1882 | Firm:Weekly Bucket (Monday through Sunday) | 110 | 2/20/2017 | 65 | |
| 26 | WOULD BE IDEAL RESULT FOR (F) |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Results
On the results tab everything highlighted in yellow is what would have changed. Items not highlighted in yellow there was no sale. The green and red formatting was for reference and is not required if to difficult. (would be ideal) column H, is illustrating, if this is even possible, once the order quantity reaches 0, for the script to keep searching sheet for next order with same criteria and subtract remain quantity. Anything will help, I am currently doing this manually over the weekend and generally 150+ sales per week and is very time consuming.
In simple terms, I am trying to have a script go through each sale row and subtract quantity from the order sheet if the plant and part number are the same. I will sort the order sheet based on when they are do so it removes from correct order.
Thanks for any help!
