Need Help with Index, Match, Match with multiple results and copying data x times

Thank you so much, Mumps1! I uploaded the file with just the two worksheets and "sanitized" information! I would have sent my "attempt" at the code, but frankly, it was just darn embarrassing at how frustrated and convoluted I was getting

First Off, Thank you so very much for your attention to this little challenge. It literally kept me up all night trying to come up with a solution. Let explain a bit further. On ws1, If you look just at Dest Warehouse ACTF and Expected Ready Date of 5/20/2019, there are 13 rows, as follows: I used filter to select vendor and date for my examples

Dest Warehouse Best Vendor Projected Containers Expected Ready Date Vessel ETA Dte
ACTF CONJU 0 5/20/2019 5/15/2019
ACTF DA 0 5/20/2019 5/15/2019
ACTF GFS 0 5/20/2019 5/15/2019
ACTF GCJD 0 5/20/2019 5/15/2019
ACTF GS 0 5/20/2019 5/15/2019
ACTF LSE 0 5/20/2019 5/15/2019
ACTF MF 0 5/20/2019 5/15/2019
ACTF MYR 0 5/20/2019 5/15/2019
ACTF NFS 0 5/20/2019 5/15/2019
ACTF NTG 0 5/20/2019 5/15/2019
ACTF PCF 2 5/20/2019 5/15/2019 <---------- This Vendor(PCF) has two trucks available to go to this destination on this day.
ACTF PXG 0 5/20/2019 5/15/2019
ACTF TI 0 5/20/2019 5/15/2019

If you look at ws2, Dest Warehouse = ACTF and PickupDate = 5/20/19, there are two rows indicating that there are two containers that will be ready for pickup, as follows:
Dest Warehouse Best Vendor Projected Containers Expected Ready Date Vessel ETA Dte

Dest Warehouse Vessel ETA Dte Priority Container ID# Status Pickup Date VENDOR ASSIGNMENT
ACTF 5/15/2019 4 7014876 Not Available 5/20/2019
ACTF 5/15/2019 3 6811681 Not Available 5/20/2019

What I would like to do is put the vendor code in the Vendor Assignment column.

Now, sometimes, there are containers ready to ship, but no vendor trucks available. In this case, Vendor Assignment can remain blank.

Other times, there may be more than one vendor available for the containers, as follows:

ws1
Dest Warehouse Best Vendor Projected Containers Expected Ready Date Vessel ETA Dte
PTC2 CONJU 0 5/24/2019 5/19/2019
PTC2 DA 0 5/24/2019 5/19/2019
PTC2 GFS 0 5/24/2019 5/19/2019
PTC2 GCJD 0 5/24/2019 5/19/2019
PTC2 GS 12 5/24/2019 5/19/2019 So, here we have 3 vendors that can handle 22 total containers
PTC2 LSE 8 5/24/2019 5/19/2019
PTC2 MF 0 5/24/2019 5/19/2019
PTC2 MYR 0 5/24/2019 5/19/2019
PTC2 NFS 0 5/24/2019 5/19/2019
PTC2 NTG 0 5/24/2019 5/19/2019
PTC2 PCF 2 5/24/2019 5/19/2019
PTC2 PXG 0 5/24/2019 5/19/2019
PTC2 TI 0 5/24/2019 5/19/2019

ws2:
Dest Warehouse Vessel ETA Dte Priority Container ID# Status Pickup Date VENDOR ASSIGNMENT
PTC2 5/19/2019 3 8395544 Not Available 5/24/2019
PTC2 5/19/2019 3 9694821 Not Available 5/24/2019
PTC2 5/19/2019 3 6827141 Not Available 5/24/2019
PTC2 5/19/2019 3 8430773 Not Available 5/24/2019
PTC2 5/19/2019 3 4979719 Not Available 5/24/2019
PTC2 5/19/2019 3 6815265 Not Available 5/24/2019
PTC2 5/19/2019 3 7844281 Not Available 5/24/2019
PTC2 5/19/2019 3 8104976 Not Available 5/24/2019 And here we have 22 total containers
PTC2 5/19/2019 3 9586742 Not Available 5/24/2019
PTC2 5/19/2019 3 3183749 Not Available 5/24/2019
PTC2 5/19/2019 3 2530352 Not Available 5/24/2019
PTC2 5/19/2019 3 5446412 Not Available 5/24/2019
PTC2 5/19/2019 3 8034765 Not Available 5/24/2019
PTC2 5/19/2019 3 1154503 Not Available 5/24/2019
PTC2 5/19/2019 3 6770545 Not Available 5/24/2019
PTC2 5/19/2019 3 1588995 Not Available 5/24/2019
PTC2 5/19/2019 3 3806203 Not Available 5/24/2019
PTC2 5/19/2019 3 7809199 Not Available 5/24/2019
PTC2 5/19/2019 3 6637069 Not Available 5/24/2019
PTC2 5/19/2019 3 5867987 Not Available 5/24/2019
PTC2 5/19/2019 3 9809261 Not Available 5/24/2019
PTC2 5/19/2019 3 2698256 Not Available 5/24/2019


In this case, I need to assign all 24 vendors to all 24 containers and it doesn't really matter which vendor gets which container.

Basically, I took all the information from ws2 to determine the number of containers needed to be delivered to each destination warehouse by each day (that we had data on). Then, I used solver to determine the most cost effective vendor match for each day, since each vendor has a different price for east dest warehouse. Right now, I have the data in solver for ACTF, FTCI, PL17, PLC1, PSSVCCI, PTC2 and PTC3, so that is what is on ws1. I used these because they typically have the largest volume.

I had to loop solver through each day, because of the 200 variable limitation. And, I have to ultimately forecast out 30-60 days at times.

Thank so much! Would it be easier if I added a column where date1 and dest warehouse were combined on each sheet? So it would be matching one column with unique cells to one column with duplicate cells? The grabbing the vendor from ws1 and adding it to the match in Washington 2? x times, then next? I really hate to give up on things, I am a Taurus! Lol

You are a miracle worker! I uploaded a file with the reference of Dest Warehouse/Date for each worksheet. It is call Excel test Revision 1 !!! Would it be too early to say I LOVE YOU!!!!! You are so awesome!

I seriously can not thank you enough! Your work helped me create a workbook that imports container data for over 5,000 containers consisting with more than 25 destinations, then uses solver to find the most cost efficient combination of carriers and estimates the cost per day by destination and then assigns the shipping company to each container. My dashboard allows the end user to select dates they want to see coverage of, and projects 60 days in advance. I have graphs, cost forecasts and shipping budgets. This is a tremendous improvement over the current process! I could not have finished this project without you! I am immensely happy with the outcome and I credit you with putting the icing on the cake! You are amazing!