Choose next package if the package starts with a specific number.

C

CaraEL

New Member
Joined
Nov 21, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I need some help :giggle:

My formula below in column C looks into the sheet Program and finds the first best transport after the delivery date in column B. If duplicate matches occur the formula chooses the next best package instead in order to avoid the same ppackage being booked for two or several transports. So far this is correct, however I would like to add a condition to it and that condition is that if the package that is choosen is a package starting with 2 (the 2000 package series) I would like the formula to ignore that package and instead choose the first best package that starts with a 1 (the 1000 series). Is this possible?

Regards
C

Sheet 1 - Delivery
Package noReady for DeliveryMatch against transport
Column A * see formula belowColumn BColumn C ** see formula belox
10312021-05-182021-05-21
20012021-09-282021-10-16 want package number 1038 instead
20022022-01-192022-02-01 want package number 1042 instead
10372022-04-112022-05-14
10562022-06-282022-07-26
20062023-06-142023-06-22 want package number 1073 instead
10742023-08-092023-08-22

* =VLOOKUP(C2;'Program'!$R:$S;2;FALSE)

**=IF(ISNUMBER(MATCH(INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+3);$C$1:C2;0));INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+4);IF(ISNUMBER(MATCH(INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+2);$C$1:C2;0));INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+3);IF(ISNUMBER(MATCH(INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+1);$C$1:C2;0));INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+2);INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+1))))

Sheet 2 - Program
Delivery Pick upPackage no
Column RColumn S
2021-03-251030
2021-05-211031
2021-07-021033
2021-08-311036
2021-10-162001
2021-11-041038
2022-01-061032
2022-02-012002
2022-02-171042
2022-03-291043
2022-05-141037
2022-05-192004
2022-06-221055
2022-07-261056
2022-08-111057
2023-05-251069
2023-06-242006
2023-08-081073
2023-08-221074
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Peter_SSs said:
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...
Thanks for the advise, my details are updated.
 
Upvote 0
CaraEL said:
my details are updated.
Click to expand...
Thanks for that. (y)

Now I'm just trying to understand what you are trying to do. Could you talk us through how you would manually do say the first two rows of the Delivery sheet in your sample above?
 
Upvote 0
Take row 2 as an exemplet.
Column B has a fixade Finish date when the packages are reade for pick up (2021-09-28).
Column C (via the IF function) looks at the date in Column B (2021-09-28) and goes to sheet Program Column R and looks for the first date after the date in Column B (2021-09-28) and returns it (2021-10-16). Then the function also looks at the above dates in Column C ($C$1:C2) and if a date occurs more than once the function should pick a new date that is the next in line and return it instead.
Column A VLOOKUPs the date in Column C (2021-10-16) goes to sheet Program Column R (2021-10-16) and returns the package no in Column S (2001).
What I hope to do is that if a returned package belongs to the 2000-series I want the funktion to continue and look for the first best 1000-series package instead.
 
Upvote 0
CaraEL said:
returns it (2021-10-16). Then the function also looks at the above dates in Column C ($C$1:C2) and if a date occurs more than once
Click to expand...
This process you just described returns 2021-10-16. When I look at the cells above that date in column C the only other date I see is 2021-05-21 so no date is occurring more than once so why do we look for something else?
CaraEL said:
Sheet 1 - Delivery
Package noReady for DeliveryMatch against transport
Column A * see formula belowColumn BColumn C ** see formula belox
10312021-05-182021-05-21
20012021-09-282021-10-16 want package number 1038 instead
Click to expand...


CaraEL said:
continue and look for the first best 1000-series package instead.
Click to expand...
Sorry, I have no idea what a "first best 1000-series package" is.

Still pretty much completely lost I'm afraid.
 
Upvote 0
Peter_SSs said:
This process you just described returns 2021-10-16. When I look at the cells above that date in column C the only other date I see is 2021-05-21 so no date is occurring more than once so why do we look for something else?




Sorry, I have no idea what a "first best 1000-series package" is.

Still pretty much completely lost I'm afraid.
Click to expand...
In my original file which has several hundred row there are duplicates that need to be identified that's why the function looks for that.

Regarding the 1000-series package what I meant was that if a package that is choose starts with 2 (2001, 2002 etc the 2000-series) I want the function to ignore that package and instead choose a package starting with 1 (1038, 1042, 1055 etc the 1000-series). Sorry for not being able to explain it better.
 
Upvote 0
I've now published a short version of the programme below.

Column C: Here I want to find the first available date after the date i column B on the same row. The function looks at column B (Delivery) then searches column A (Program) for the closest date after that in B and returns it. I have put in a condition that if the function returns dates in column C that are duplicates then the function should continue searching in column A (Program) for the second date that is available.

Ex. row 2:
The date we are looking at to match a transport with is 9/28/2021 (Column B) and in column C we want to return the first available date after September 28th 2021. In column A (Program) we find 10/16/2022 and return it in column C (Delivery). The function then looks at row 1 to make sure that the date 10/16/2022 has not been used for row 1. If the date hasn't been used 10/16/2022 is kept as a date but if 10/16/2022 had been used the function would have continued to search column A (Program) a date and would have returned 11/4/2021 instead.

Column A (Delivery): We look at the date in column C (Delivery), find that date in column A (Program) and return the package number on the same row. So in this example we look for the date October 16th 2021 and return the package number 2001.

All this is ok and works fine. However I want to add a condition that will ignore to choose the packages 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008 and instead choose a package number starting with 1XXX. In this example I would like the function on row 2 to instead of returning 2001 Column A (Delivery) and 10/16/2021 Column C (Devlivery) I would like the function to return 1038 in Column A (Delivery) and 11/4/2021 in Column C (Delivery).
And for row 3 instead of returning 2002 Column A (Delivery) and 2/1/2022 Column C (Devlivery) I would like the function to return 1042 in Column A (Delivery) and 2/17/2022 in Column C (Delivery).

I hope this is a bit clearer, if not then I will have to try and solve it myself od hope someone else can help me :).

Delivery
Package no (Column A)Ready for Delivery (Column B)Matching againts transport (Column C)
=VLOOKUP(C3,Program!$A:$B,2,FALSE)5/18/2021=IF(ISNUMBER(MATCH(INDEX(Program!$A$2:$A$98,MATCH(B3,Program!$A$2:$A$98,1)+3),$C$2:C2,0)),INDEX(Program!$A$2:$A$98,MATCH(B3,Program!$A$2:$A$98,1)+4),IF(ISNUMBER(MATCH(INDEX(Program!$A$2:$A$98,MATCH(B3,Program!$A$2:$A$98,1)+2),$C$2:C2,0)),INDEX(Program!$A$2:$A$98,MATCH(B3,Program!$A$2:$A$98,1)+3),IF(ISNUMBER(MATCH(INDEX(Program!$A$2:$A$98,MATCH(B3,Program!$A$2:$A$98,1)+1),$C$2:C2,0)),INDEX(Program!$A$2:$A$98,MATCH(B3,Program!$A$2:$A$98,1)+2),INDEX(Program!$A$2:$A$98,MATCH(B3,Program!$A$2:$A$98,1)+1))))
20019/28/202110/16/2021
20021/19/20222/1/2022
10374/11/20225/14/2022
10566/28/20227/26/2022
10419/2/20229/6/2022
106211/9/202211/18/2022
10651/31/20232/1/2023
10684/4/20234/5/2023
20066/14/20236/22/2023
10748/9/20238/22/2023
107710/5/202310/11/2023
108212/12/202312/20/2023
10712/26/20242/27/2024
10764/23/20245/16/2024
10904/24/20245/25/2024
10914/25/20246/8/2024
10934/26/20247/6/2024


Program
Start trp (Column A)Package no (Column B)
7/17/20191011
11/5/20191012
12/19/20191014
1/24/20201015
2/19/20201016
4/1/20201017
10/16/20201018
11/27/20201019
12/30/20201020
1/29/20211021
2/19/20211022
3/10/20211023
3/25/20211030
5/21/20211031
7/2/20211033
7/22/20211034
8/13/20211035
8/31/20211036
10/16/20212001
11/4/20211038
11/19/20211039
12/7/20219082
12/25/20211040
1/6/20221032
2/1/20222002
2/17/20221042
3/10/20222003
3/29/20221043
5/14/20221037
5/19/20222004
6/22/20221055
7/26/20221056
8/11/20221057
8/27/20221058
9/6/20221041
9/13/20221059
10/5/20221060
10/21/20221061
11/18/20221062
12/7/20221063
12/22/20221064
1/11/20231044
2/1/20231065
2/10/20231066
2/28/20232005
3/18/20231067
4/5/20231068
5/25/20231069
5/31/20239084
6/22/20232006
6/24/20231070
7/25/20231072
12/19/20191073
8/22/20231074
9/1/20232007
9/5/20231075
9/14/20232008
10/11/20231077
10/25/20231078
11/8/20231079
12/6/20231081
12/20/20231082
1/5/20241083
2/3/20241085
2/21/20241086
2/27/20241071
3/6/20241087
3/22/20241088
5/16/20241076
5/25/20241090
6/8/20241091
7/6/20241093
7/16/20241080
8/3/20241095
8/17/20241096
8/31/20241097
9/14/20241098
9/17/20241084
10/15/20241099
10/29/20241100
11/12/20241101
11/26/20241102
11/29/20241089
12/10/20241103
12/24/20241104
1/7/20251105
1/23/20251106
2/6/20251092
2/8/20251107
2/22/20251108
3/13/20251109
3/25/20251094
3/28/20251110
5/17/20251111
5/31/20251112
6/14/20251113
6/28/20251114
 
Upvote 0
CaraEL said:
hope this is a bit clearer,
Click to expand...
Much, thank you.

See if this is it. Note that my dates are d/m/y format.

CaraEL.xlsm
AB
1Start trpPackage no
217/07/20191011
35/11/20191012
419/12/20191014
524/01/20201015
619/02/20201016
71/04/20201017
816/10/20201018
927/11/20201019
1030/12/20201020
1129/01/20211021
1219/02/20211022
1310/03/20211023
1425/03/20211030
1521/05/20211031
162/07/20211033
1722/07/20211034
1813/08/20211035
1931/08/20211036
2016/10/20212001
214/11/20211038
2219/11/20211039
237/12/20219082
2425/12/20211040
256/01/20221032
261/02/20222002
2717/02/20221042
2810/03/20222003
2929/03/20221043
3014/05/20221037
3119/05/20222004
3222/06/20221055
3326/07/20221056
3411/08/20221057
3527/08/20221058
366/09/20221041
3713/09/20221059
385/10/20221060
3921/10/20221061
4018/11/20221062
417/12/20221063
4222/12/20221064
4311/01/20231044
441/02/20231065
4510/02/20231066
4628/02/20232005
4718/03/20231067
485/04/20231068
4925/05/20231069
5031/05/20239084
5122/06/20232006
5224/06/20231070
5325/07/20231072
5419/12/20191073
5522/08/20231074
561/09/20232007
575/09/20231075
5814/09/20232008
5911/10/20231077
6025/10/20231078
618/11/20231079
626/12/20231081
6320/12/20231082
645/01/20241083
653/02/20241085
6621/02/20241086
6727/02/20241071
686/03/20241087
6922/03/20241088
7016/05/20241076
7125/05/20241090
728/06/20241091
736/07/20241093
7416/07/20241080
753/08/20241095
7617/08/20241096
7731/08/20241097
7814/09/20241098
7917/09/20241084
8015/10/20241099
8129/10/20241100
8212/11/20241101
8326/11/20241102
8429/11/20241089
8510/12/20241103
8624/12/20241104
877/01/20251105
8823/01/20251106
896/02/20251092
908/02/20251107
9122/02/20251108
9213/03/20251109
9325/03/20251094
9428/03/20251110
9517/05/20251111
9631/05/20251112
9714/06/20251113
9828/06/20251114
Program


CaraEL.xlsm
ABC
1Package noReady for DeliveryMatching against transport
2103118/05/202121/05/2021
3103828/09/20214/11/2021
4104219/01/202217/02/2022
5103711/04/202214/05/2022
6105628/06/202226/07/2022
710412/09/20226/09/2022
810629/11/202218/11/2022
9106531/01/20231/02/2023
1010684/04/20235/04/2023
11107014/06/202324/06/2023
1210749/08/202322/08/2023
1310775/10/202311/10/2023
14108212/12/202320/12/2023
15107126/02/202427/02/2024
16107623/04/202416/05/2024
17109024/04/202425/05/2024
18109125/04/20248/06/2024
19109326/04/20246/07/2024
Delivery
Cell Formulas
RangeFormula
A2:A19A2=VLOOKUP(C2,Program!A$2:B$98,2,0)
C2:C19C2=AGGREGATE(15,6,Program!A$2:A$98/((Program!A$2:A$98>B2)*ISNA(MATCH(Program!A$2:A$98,C$1:C1,0))*(Program!B$2:B$98<2000)),1)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

A
  • Solved
Returning a single result for a match under certain criteria
Replies
5
Views
382
ATHlaptop
A
SaraWitch
  • Question
Highlight cell if text matches in range of cells, but only if other cells are blank
2
Replies
11
Views
328
SaraWitch
SaraWitch
SaraWitch
  • Solved
VLOOKUP formula with multiple IF statements
Replies
2
Views
454
SaraWitch
SaraWitch
F
  • Question
Looking to index some data based on multiple criteria, then index further data based on the 1st indexed data results
2
Replies
13
Views
495
KRice
K
S
  • Solved
IF function excluding 0 and 9 in text string
Replies
4
Views
148
dreid1011
dreid1011

Forum statistics

Threads
1,214,834
Messages
6,121,876
Members
449,056
Latest member
ruhulaminappu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top