Hi,
I seem to be able to use index/match for a specific scenario but struggling to apply it to others so looking for some help - maybe that's not even the best way of doing it.
I have two data sources a list of products and another data source with the products, order despatch dates and shortages.
<tbody>
</tbody>
The source data I'm looking to draw from is similar to the below:
<tbody>
</tbody>
I'm looking for a formula that will return the first time we're going to be short on a product and the date that the first shortage will occur. Here are the results i'd expect below.
<tbody>
</tbody>
How am i best to do it? Any ideas?
Thanks in advance.
L
I seem to be able to use index/match for a specific scenario but struggling to apply it to others so looking for some help - maybe that's not even the best way of doing it.
I have two data sources a list of products and another data source with the products, order despatch dates and shortages.
A | B | C | |
1 | Product | First Shortage | First Shortage Date |
2 | 123 | ||
3 | 456 | ||
4 | 789 |
<tbody>
</tbody>
The source data I'm looking to draw from is similar to the below:
A | B | C | |
1 | Product | Order Date | Shortage |
2 | 123 | 02/05/2018 | 0 |
3 | 123 | 15/06/2018 | 0 |
4 | 123 | 17/07/2018 | 5 |
5 | 456 | 01/01/2019 | 1 |
6 | 456 | 02/01/2019 | 2 |
7 | 456 | 02/02/2019 | 10 |
8 | 456 | 03/03/2019 | 15 |
9 | 789 | 01/12/2017 | 0 |
10 | 789 | 01/01/2018 | 5 |
<tbody>
</tbody>
I'm looking for a formula that will return the first time we're going to be short on a product and the date that the first shortage will occur. Here are the results i'd expect below.
A | B | C | |
1 | Product | First Shortage | First Shortage Date |
2 | 123 | 5 | 17/07/2018 |
3 | 456 | 1 | 01/01/2019 |
4 | 789 | 5 | 01/01/2018 |
<tbody>
</tbody>
How am i best to do it? Any ideas?
Thanks in advance.
L