Check if specific date has been used and if yes choose first next available date

CaraEL

New Member
Joined
Nov 21, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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

Programme SheetMatching Sheet
Transport Date (A)Package no (B)Package no (A)Finish Date (B)Match against package (C)
2022-01-051041=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-06103210592022-07-282022-08-05
2022-02-04104210642022-10-132022-10-21
2022-02-19104310662022-12-202022-12-30
2022-03-08104410702023-03-012023-03-02
2022-04-09105510752023-05-242023-05-27
2022-04-26105610772023-08-012023-08-03
2022-05-06103910812023-10-032023-10-04
2022-05-13105710852023-11-292023-12-13
2022-05-31105810872024-01-022024-01-12
2022-07-22200510892024-02-082024-02-13
2022-08-05105910912024-03-122024-03-16
2022-08-20106010942024-04-252024-05-07
2022-09-06106110862024-05-302024-07-18
2022-09-2110621086 Should show 10972024-06-302024-07-18 Should show 2024-07-31
2022-10-0610631097 Should show 10982024-07-292024-07-31 Should show 2024-08-10
2022-10-21106410992024-08-272024-08-30
2022-11-052006
2022-11-221065
2022-12-1384
2022-12-301066
2023-01-251067
2023-02-181069
2023-02-212007
2023-03-021070
2023-03-241071
2023-04-211073
2023-05-102008
2023-05-101074
2023-05-271075
2023-08-031077
2023-08-181078
2023-09-021079
2023-09-191068
2023-10-041081
2023-10-191082
2023-11-031083
2023-11-141072
2023-11-181084
2023-12-131085
2024-01-121087
2024-02-011076
2024-02-131089
2024-02-281090
2024-03-161091
2024-04-021092
2024-04-201080
2024-05-071094
2024-05-241095
2024-07-181086
2024-07-311097
2024-08-101088
2024-08-151098
2024-08-301099
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this at C2 & Drag it Down:
Excel Formula:
=IF(ISNUMBER(MATCH(INDEX(Programme!$A$1:$A$56,MATCH(B2,Programme!$A$1:$A$56,1)+1),$C$1:C1,0)),INDEX(Programme!$A$1:$A$56,MATCH(B2,Programme!$A$1:$A$56,1)+2),INDEX(Programme!$A$1:$A$56,MATCH(B2,Programme!$A$1:$A$56,1)+1))
If your system works with SemiColon Try this:
Excel Formula:
=IF(ISNUMBER(MATCH(INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+1);$C$1:C1;0));INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+2);INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+1))
 

CaraEL

New Member
Joined
Nov 21, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Try this at C2 & Drag it Down:
Excel Formula:
=IF(ISNUMBER(MATCH(INDEX(Programme!$A$1:$A$56,MATCH(B2,Programme!$A$1:$A$56,1)+1),$C$1:C1,0)),INDEX(Programme!$A$1:$A$56,MATCH(B2,Programme!$A$1:$A$56,1)+2),INDEX(Programme!$A$1:$A$56,MATCH(B2,Programme!$A$1:$A$56,1)+1))
If your system works with SemiColon Try this:
Excel Formula:
=IF(ISNUMBER(MATCH(INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+1);$C$1:C1;0));INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+2);INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+1))
Hi, thanks for the help. The formula works for the 5 first rows but all after that it get a #REF! Error. So from the row that should show 2023-05-27 and all thereafter have #REF! errors.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please check your formula at first row show ref Error, Maybe Input mistaken or don't fix range address.
Or upload first row formula shows error at here.
 

CaraEL

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

ADVERTISEMENT

Please check your formula at first row show ref Error, Maybe Input mistaken or don't fix range address.
Or upload first row formula shows error at here.
Just managed to fix the problem. I had made a small input error, it works now. Thank you very much for the help, really appreciated :).
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & thanks for Feedback.
 

CaraEL

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

ADVERTISEMENT

You're Welcome & thanks for Feedback
Sorry, one more question. If there are 3 or more dates that are the same how should the formula be then? I supposed it should state INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+3) and so on, am I correct? Where should I put the +3 so it's in the correct place?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,645
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
Excel Formula:
=IF(ISNUMBER(MATCH(INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+2);$C$1:C1;0));INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+3);IF(ISNUMBER(MATCH(INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+1);$C$1:C1;0));INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+2);INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+1)))
 
Solution

CaraEL

New Member
Joined
Nov 21, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Try this:
Excel Formula:
=IF(ISNUMBER(MATCH(INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+2);$C$1:C1;0));INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+3);IF(ISNUMBER(MATCH(INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+1);$C$1:C1;0));INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+2);INDEX(Programme!$A$1:$A$56;MATCH(B2;Programme!$A$1:$A$56;1)+1)))
Works perfectly, thanks 😀
 

Watch MrExcel Video

Forum statistics

Threads
1,127,778
Messages
5,626,827
Members
416,202
Latest member
donya ba

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
Top