Hi,
I'm pretty new to this and try a lot to solve things by myself but have now come to a point where I need help.
I need to be able to check if a specific date has previously been used and if it has then choose the first next available date.
In sheet Programme I have Transport Date (A) and Package no (B).
In sheet Matching I have Finish Date (B) which shows the date when the items are available for packing.
Match Against Package (C) should check the Finish Date and choose the first best Transport Date after that. Package no (A in sheet Matching) should look up the date in column C, find the matching date in sheet Programme and return the Package no that is joined to that date.
This part I think I have managed to get working. The problem occurs when two or more of the same dates/packages are returned (dates an package no in red). How do I write the formula to not choose the same dates and instead choose the first next available date? Is this possible without VBA?
The blue formulas return the dates/package no in the rows below.
I hope this information is good enough so someone might be able to help me.
Regards
Cara
I'm pretty new to this and try a lot to solve things by myself but have now come to a point where I need help.
I need to be able to check if a specific date has previously been used and if it has then choose the first next available date.
In sheet Programme I have Transport Date (A) and Package no (B).
In sheet Matching I have Finish Date (B) which shows the date when the items are available for packing.
Match Against Package (C) should check the Finish Date and choose the first best Transport Date after that. Package no (A in sheet Matching) should look up the date in column C, find the matching date in sheet Programme and return the Package no that is joined to that date.
This part I think I have managed to get working. The problem occurs when two or more of the same dates/packages are returned (dates an package no in red). How do I write the formula to not choose the same dates and instead choose the first next available date? Is this possible without VBA?
The blue formulas return the dates/package no in the rows below.
I hope this information is good enough so someone might be able to help me.
Regards
Cara
| Programme Sheet | Matching Sheet | ||||
|---|---|---|---|---|---|
| Transport Date (A) | Package no (B) | Package no (A) | Finish Date (B) | Match against package (C) | |
| 2022-01-05 | 1041 | =VLOOKUP(C2;Programme!$A:$B;2;FALSE) | 2022-05-15 | =INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+1) | |
| 2022-01-06 | 1032 | 1059 | 2022-07-28 | 2022-08-05 | |
| 2022-02-04 | 1042 | 1064 | 2022-10-13 | 2022-10-21 | |
| 2022-02-19 | 1043 | 1066 | 2022-12-20 | 2022-12-30 | |
| 2022-03-08 | 1044 | 1070 | 2023-03-01 | 2023-03-02 | |
| 2022-04-09 | 1055 | 1075 | 2023-05-24 | 2023-05-27 | |
| 2022-04-26 | 1056 | 1077 | 2023-08-01 | 2023-08-03 | |
| 2022-05-06 | 1039 | 1081 | 2023-10-03 | 2023-10-04 | |
| 2022-05-13 | 1057 | 1085 | 2023-11-29 | 2023-12-13 | |
| 2022-05-31 | 1058 | 1087 | 2024-01-02 | 2024-01-12 | |
| 2022-07-22 | 2005 | 1089 | 2024-02-08 | 2024-02-13 | |
| 2022-08-05 | 1059 | 1091 | 2024-03-12 | 2024-03-16 | |
| 2022-08-20 | 1060 | 1094 | 2024-04-25 | 2024-05-07 | |
| 2022-09-06 | 1061 | 1086 | 2024-05-30 | 2024-07-18 | |
| 2022-09-21 | 1062 | 1086 Should show 1097 | 2024-06-30 | 2024-07-18 Should show 2024-07-31 | |
| 2022-10-06 | 1063 | 1097 Should show 1098 | 2024-07-29 | 2024-07-31 Should show 2024-08-10 | |
| 2022-10-21 | 1064 | 1099 | 2024-08-27 | 2024-08-30 | |
| 2022-11-05 | 2006 | ||||
| 2022-11-22 | 1065 | ||||
| 2022-12-13 | 84 | ||||
| 2022-12-30 | 1066 | ||||
| 2023-01-25 | 1067 | ||||
| 2023-02-18 | 1069 | ||||
| 2023-02-21 | 2007 | ||||
| 2023-03-02 | 1070 | ||||
| 2023-03-24 | 1071 | ||||
| 2023-04-21 | 1073 | ||||
| 2023-05-10 | 2008 | ||||
| 2023-05-10 | 1074 | ||||
| 2023-05-27 | 1075 | ||||
| 2023-08-03 | 1077 | ||||
| 2023-08-18 | 1078 | ||||
| 2023-09-02 | 1079 | ||||
| 2023-09-19 | 1068 | ||||
| 2023-10-04 | 1081 | ||||
| 2023-10-19 | 1082 | ||||
| 2023-11-03 | 1083 | ||||
| 2023-11-14 | 1072 | ||||
| 2023-11-18 | 1084 | ||||
| 2023-12-13 | 1085 | ||||
| 2024-01-12 | 1087 | ||||
| 2024-02-01 | 1076 | ||||
| 2024-02-13 | 1089 | ||||
| 2024-02-28 | 1090 | ||||
| 2024-03-16 | 1091 | ||||
| 2024-04-02 | 1092 | ||||
| 2024-04-20 | 1080 | ||||
| 2024-05-07 | 1094 | ||||
| 2024-05-24 | 1095 | ||||
| 2024-07-18 | 1086 | ||||
| 2024-07-31 | 1097 | ||||
| 2024-08-10 | 1088 | ||||
| 2024-08-15 | 1098 | ||||
| 2024-08-30 | 1099 |
.
