Date Slot allocation based on slot available and ETA Date

K

khankeong

New member
Joined
Dec 28, 2022
Messages
4
Reaction score
0
Points
1
Excel Version(s)
2021
Hi All,
I have 2 table. 1 table is my warehouses daily available slot to receive goods. Another table is the goods ETA at port. We already preplanned which goods is to which warehouse. But I need a query to match the time slot table to goods ETA table. whichever ETA reached first will take earliest slot based on warehouse, but some of the earliest slot available is earlier than ETA. Any suggestion? Thanks.
 

Attachments

  • ALLOCATION.xlsx
    11.2 KB · Views: 4
This sort of problem does not lend itself well to being solved in Power Query (I'm sure someone will prove me wrong). I tried but failed; it got too convoluted. Maybe I was going the wrong way about it.
While trying to develop such a solution I wrote a short user-defined-function (UDF) which seems to work well (see single-celled formula in cell I3):

1672397495495.png

The results vary on the sort order of the 2 tables; the time slots table should be sorted in date order, and it's probably better if the ETA table is similarly sorted by date.
Would this (or simlar) be of interest?
If so, you've got some work to do: You will have read the link supplied by @alansidman , so before I post what I've done, what you'll need to do is supply links here, to of all your cross posts (I've seen 2 others so far), then check those sites to see if they want you to do the same there (they usually do).

If all this is too much bother for you, or if you think that what you post elsewhere is none of our business, I will still post my offering, but in about 4 months or so.

This does not prevent others here from jumping in with a solution if they want to.
 
p45cal said:
This sort of problem does not lend itself well to being solved in Power Query (I'm sure someone will prove me wrong). I tried but failed; it got too convoluted. Maybe I was going the wrong way about it.
While trying to develop such a solution I wrote a short user-defined-function (UDF) which seems to work well (see single-celled formula in cell I3):

View attachment 11289

The results vary on the sort order of the 2 tables; the time slots table should be sorted in date order, and it's probably better if the ETA table is similarly sorted by date.
Would this (or simlar) be of interest?
If so, you've got some work to do: You will have read the link supplied by @alansidman , so before I post what I've done, what you'll need to do is supply links here, to of all your cross posts (I've seen 2 others so far), then check those sites to see if they want you to do the same there (they usually do).

If all this is too much bother for you, or if you think that what you post elsewhere is none of our business, I will still post my offering, but in about 4 months or so.

This does not prevent others here from jumping in with a solution if they want to.
Click to expand...

Hi,
Sorry because it was my first time posting in any forum. It was quite urgent i needed solution on that day. But i am still very interested to know how to solve this.

Power Query Date Slot allocation based on slot available and ETA Date - Microsoft Q&A

Microsoft Q&A is the best place to get answers to all your technical questions on Microsoft products and services. Community. Forum.
learn.microsoft.com
community.powerbi.com

Date Slot allocation based on slot available and ETA Date

Hi All, I have 2 table. 1 table is my warehouses daily available slot to receive goods. Another table is the goods ETA at port. We already preplanned which goods is to which warehouse. But I need a query to match the time slot table to goods ETA table. whichever ETA reached first will take...
community.powerbi.com community.powerbi.com
 
Single-celled formula in I3
Code in Module1
 

Attachments

  • ExcelGuru11677ALLOCATION01.xlsm
    27.2 KB · Views: 2
It was quite urgent i needed solution on that day.


Ingratitude is the world's reward!

He got the solution. But this had to be used in such a hurry that there was not even time for a thank you.
 
pinarello said:
It was quite urgent i needed solution on that day.


Ingratitude is the world's reward!

He got the solution. But this had to be used in such a hurry that there was not even time for a thank you.
Click to expand...
Maybe my words make you misunderstood. Apologize if somehow offended you. English was not my native language. I was only trying to explain why i posted in multiple forum on that day because the solution needed quite urgently on that day, and it was my first time posting any power query solution.
 
You must log in or register to reply here.
Back
Top