Hello,
I am trying to use power automate to update an existing excel file using table data from another file. While consolidating the data on the updated existing file and also using the existing virtual inventory to be reapplied to the quantities to reach ordering thresholds.
Theoretically, I want to drop an excel file into a folder -- power automate would flow this data into a main existing file add the quantities of the same items while retaining "stock size" info. It would recognize the threshold for order (Quantities > Stock Size so add 1 to the order or if twice the stock size order 2, etc) -- Create a file with "Item" and "Order Quantity" -- From the existing file once that new order file is created -- update the quantities to be what was left after the order.
Example:
Existing Excel Data
| Item | Quantities | Stock Size | Order Quantity | Virtual Inventory |
| Banana | 9 | 20 | 0 | 9 |
| Apple | 4 | 10 | 0 | 4 |
| Watermelon | 9 | 10 | 0 | 9 |
| Cherry | 5 | 10 | 0 | 5 |
Data to be added
| Item | Quantities | Stock Size |
| Cherry | 50 | 10 |
| Watermelon | 20 | 10 |
| Banana | 20 | 20 |
Updated Existing Table
| Item | Quantities | Stock Size | Order Quantity | Virtual Inventory |
| Banana | 29 | 20 | 1 | 9 |
| Apple | 4 | 10 | 0 | 4 |
| Watermelon | 20 | 10 | 1 | 0 |
| Cherry | 55 | 10 | 5 | 5 |
Newly created order file
| Banana | 1 |
| Cherry | 5 |
| Watermelon | 1 |
File after order file has been created
| Item | Quantities | Stock Size | Order Quantity | Virtual Inventory |
| Banana | 9 | 20 | 0 | 9 |
| Apple | 4 | 10 | 0 | 4 |
| Watermelon | 0 | 10 | 0 | 0 |
| Cherry | 5 | 10 | 0 | 5 |
Please help. I have tried to post elsewhere with no help yet. Really need to figure this out.
I know all of this can be done manually so I know it's possible - I am just a novice at powerautomate..
