Complicated situation - have to repack goods in VBA

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
348
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Hi Guys,

Here I'm with a complicated situation, I have many styles packed goods in carton, now I have to re-pack again for air shipment, with below condition.

FILE IN GOOGLE DRIVE
Sheets are;
01.AIR-QTY
02.CURRENT-PACKING
03.DESIRED-PACKING

We will turn sheet "CURRENT-PACKING" to sheet "DESIRED-PACKING" (I have shown desired output for order no 599 & color B94, same will follow for others color accordingly).

Conditions are;
***We will match the Style no#, Order no & Col between sheet AIR-QTY & CURRENT-PACKING.
***In some cartons there are mixed sizes of goods, we won't repack those cartons.
***We will reduce/remove only complete carton from current-packing & won't break any carton.
***Quantity can't be less than the required AIR-QTY, we can keep more qty as we have to keep complete carton & we are not allowed to broke any complete carton.
***If the same size has in multiple rows (example in "desired-packing" order no-599, col-B94), then we have to calculate those (rows) qty to match with required aired qty in sheet AIR-QTY.

I want to have solutions in VB to do it faster.
How can I start?
Any help with the idea or with some code would be highly appreciated.

AIR-QTY
RE-PACKING..xlsx
ABCDEFGHIJ
4ORDERSTYLECOLOURXSSMLXLXXLTOT
5517ALLALL
6518ALLALL
7519ALLALL
8525ALLALL
9527ALLALL
10529ALL
11532ALL
12536ALLALL
13540ALLALL
14583ALLALL
15604ALLALL
16
50583PMKTRP5B94-23422013
51583PMKTRP5NS-481283035
52583PMKTRP5R11-352010
53599PMKTRP5B94-17543051545026001,830
54599PMKTRP5NS-20046056048529001,995
55599PMKTRP5R11-15253020100100
AIR-QTY


CURRENT-PACKING
RE-PACKING..xlsx
ABCDEFGHIJKLMNOPQRSTUV
8StyleOrderCarton No.ColCtns qtyPer CtnTot Qty
968101214XXSXSSMLXLXXL
10PMKTRP55831-1B94-2342211313
11PMKTRP55832-2NS-48128313535
12PMKTRP55833-3R11-35211010
13PMKTRP55994-9B94-46646276
14PMKTRP559910-22B94-451345585
15PMKTRP559923-37B94-421542630
16PMKTRP559938-38B94-4714747
17PMKTRP559939-50B94-421242504
18PMKTRP559951-57B94-43743301
19PMKTRP559958-58B94-2219414545
CURRENT PACKING


DESIRED-PACKING
RE-PACKING..xlsx
ABCDEFGHIJKLMNOPQRSTUV
8StyleOrderCarton No.ColCtns qtyPer CtnTot Qty
968101214XXSXSSMLXLXXL
10PMKTRP55831-1B94-2342211313
11PMKTRP55832-2NS-48128313535
12PMKTRP55833-3R11-35211010
13PMKTRP55994-7B94-46446184
14PMKTRP55998-17B94-451045450
15PMKTRP559918-29B94-421242504
16PMKTRP559930-30B94-4714747
17PMKTRP559931-41B94-421142462
18PMKTRP559942-47B94-43643258
19PMKTRP559948-48B94-2219414545
DESIRED-PACKING


Thanks in advance.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top