Forum Discussion

Copper Contributor

Oct 03, 2021

Solved

Providing an Availability Date

Hi, I have a task to provide the availability date for confirming Customer Orders. I have built an initial excel table with all the Demand (Customer Sales Orders) and Supply (Purchase Orders), t...

Promised Date test file (031021).xlsx12 KB

excel

Formulas and Functions

Macros and VBA

Yea_So

Oct 04, 2021

vicarma

something like this?

The main formula is:

=INDEX(
       $C5:$H$30,
       MATCH(
             0,
             FILTER(
                    $H5:$H$30,
                    $C5:$C$30=C5
             ),
             0
       ),
       2
 )

The rest of the formula is just to format the date

cheers

Vicarma Promised Date test file (031021).xlsx13 KB

Yea_So

Bronze Contributor

Oct 05, 2021

vicarma

Here ya go

cheers

Promised Date test file (051021).xlsx36 KB

vicarma

Copper Contributor

Oct 05, 2021

Yea_So

Many thanks for this - looks like this may be close to the solution.

2 new comments:
i) in the file you uploaded, you were able to reference a single cell in the "Final" table (ie "=FinalTable!$D3". When I try that, it selects the whole column name (ie "=[@Quantity]"). I have my table named also "Final", and if I use the same expression "=FinalTable!$D3" then excel opens File Explorer for me to locate (I assume) the "Final" table. Any ideas how to overcome this?

ii) the file I am working with has ~30K rows, and the INDEX/MATCH/FILTER formula seems to really slow excel down (using all laptop resources/memory) and any further filtering of the table requires ~10-mins to refresh. Is there a way to lighten the process/formula?

Many thanks for your continued help here.

Cheers.

Yea_So
Bronze Contributor
Oct 05, 2021
vicarma

Don't format the 30K rows data set to a table when creating your formula. only format it to an excel table when all formulas are working properly.

Re: the Final reference that's where you name your table, you can either change the table name in the file i gave you, or use "Finale" for the mean time.
check the name manager in your current workbook also and see if there are any useless named ranges in it and remove it.

the only really need to format the range into a table is to make it so when more data is added to the data set it will propagate the formula to the new rows.
In regard to speed, the index match is pretty optimized since it only evaluates each row once at each iteration the ranges becomes smaller and smaller going down:
Notice how the range is getting smaller at each row:

try not converting it to a table and observe the processing speed timing.

cheers