looking for similarty in scheduling of 2 or more vessels

[radicalex]

I have a very large 2+ year schedule for vessels that I am working with. I need to find easily if any of them will be in the same areas at the same time. Right now the file is as follows:

Name of vessel Name of vessel ETC
Day of the week Date Code Rank Port Arrival Depart Day of the week Date Code Rank Port Arrival Depart ETC

below has information for each of those vessels

The names of the vessels are in merged cells at the top

I need to find a way if on the next tab I can use a formula to cross check the dates that the vessels will be in the same port.

I know its a bit unusual, but hopefully someone can help.

Thank you in advance.

[ChemistB]

Too complex for this tired old brain.
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[radicalex]

Thank you for the reply here is a mock book of what I am looking for. I have placed the small index match formula to help me find the port of any ship on a given day.

The help I really need is for the formula to show me which other ships are in that same port on that same day.

The mock book has all the "Ship" names and the ports and dates.
Thank you again.
I think I uploaded the file. Please let me know if it is not.

[GeoffW283]

The following formula in B37 committed as an array formula with CTRL-SHIFT-ENTER does, I think, give you what you want. Copy the formula down col-B as many rows as you need.

Please Login or Register  to view this content.

I updated your workbook with the above, it is attached below.

Let me know if this works for you.


EDIT >>>>>> The above formula and attached workbook have issues. I won't fix these unless there is any interest. This solution, I think, is superseded by ChemistB's solution below.

[ChemistB]

Okay, First, I created a list of ports in Sheet2 so I could use them in a drop-down menu (using Data Validation).

In Sheet3, I have the port we’re interested in, in cell B2, the date in B3
In C2 copied to the right, I have this ARRAYED Formula

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Questions?

[radicalex]

Thank you very much!!!! This is almost exactly what I was looking for. Only issue is it seems when Ship 22 is below ship 4 it will not show ship 4 on the list. Seem like the reference ship has to be on the list above the result ships....Is there anything we can do about that?

Basically the formula does not look above the reference ship in the row only below.

[ChemistB]

Updated my spreadsheet (rows 7 and down in sheet3) to use a reference ship to make the list.

[radicalex]

Thank you all so much for the help. It works beautifully. I can now see all ships in any port at any date without issues.

Thank you again.