Request help using INDEX MATCH

Erik D

I'm trying to identify conflicting dates and locations for assets. The assets are entered into the sheet as new rows from a form. There are two columns: [Scheduled Arrival Date] and [Arrival Wet Slip]. I'm trying to use INDEX MATCH to identify when an asset's [Scheduled Arrival Date] and [Arrival Wet Slip] conflict with another asset so our team can reschedule the conflict.

Here is the formula I have in a helper column: =INDEX([Scheduled Arrival Date]:[Arrival Wet Slip],MATCH([Scheduled Arrival Date]@row&[Arrival Wet Slip]@row,[Scheduled Arrival Date]:[Scheduled Arrival Date]&[Arrival Wet Slip]:[Arrival Wet Slip],0))

Am I misusing INDEX MATCH, making a formula error, or is there a much simpler way of doing this?

KPH

That is not the syntax for a smartsheet INDEX MATCH formula, and I'm not confident I understand what you want to do well enough to come up with an alternative.

Do both your columns have dates in them? Are you trying to create a list of dates that are duplicated? So you don't have 2 rows with the same Scheduled Arrival Date or two rows with the same Arrival Wet Slip? Or is there more going on?

Erik D

@KPH, Thank you for the reply. I'll try to explain better below:

I have three columns our schedulers use:

Asset [Scheduled Arrival Date] Column (Date)

Asset [Arrival Wet Slip] Column (Dropdown values)

Asset [Scheduled Haul Date] Column (Date)

Helper Column 1 -> returns "1" if slip matches any other slip: =IF([Arrival Wet Slip]@row = "", 0, IF(COUNTIF([Arrival Wet Slip]:[Arrival Wet Slip], [Arrival Wet Slip ]@row = [Arrival Wet Slip ]@row) > 1, 1, 0))

assuming slips conflict, I was to check for conflicting dates: if boat 2 arrival < boat 1 arrival < boat 2 haul OR boat 1 arrival < boat 2 haul < boat 1 haul, then conflict, else blank) Something like this:

=IF(Helper Column 1 = 1, IF(Check to see if asset arrival is between the dates of the asset with the matching slip OR asset with matching slip arrival is between the dates of the arriving asset, 1, ""), "")

I think I need to use INDEX MATCH to pull the arrival and haul dates of the asset with the matching slip to compare them and check for conflict.

KPH

Thanks @Erik D

I have mocked up my own schedule to match yours, so we have something to look at while we work through the formula.

Helper Column 1 is telling us where there could be a potential conflict. Namely N1 and N2 in my case. We have more than one boat using those slips. The formula you have in Helper Column 1 is:

=IF([Arrival Wet Slip]@row = "", 0, IF(COUNTIF([Arrival Wet Slip]:[Arrival Wet Slip], [Arrival Wet Slip]@row = [Arrival Wet Slip]@row) > 1, 1, 0))

This is where I believe you are. And now you want to know if those conflicts are a problem or not.

If the arrival date for the second boat is after the arrival date for the first boat and before the scheduled haul date for the first boat you will have a slip conflict.

So, in my example Amazon and Magnolia are an issue but Swallow and Dawn Treader are not.

I hope this is correct. I have played around with a few formula to try and get something that works for all situations (I have assumed that we can't rely on the rows being in chronological order). I think this does what you need...

Step 1

Start with a COLLECT function. This enables you to find all the Boat names where there is an issue:

COLLECT(Boat:Boat, [Arrival Wet Slip]:[Arrival Wet Slip], [Arrival Wet Slip]@row, [Scheduled Arrival Date]:[Scheduled Arrival Date], <=[Scheduled Arrival Date]@row, [Scheduled Haul Date]:[Scheduled Haul Date], >=[Scheduled Arrival Date]@row)

You can't use COLLECT on it's own, but what this says is to gather all the cells in the Boat column (that is one I added, you probably have another, more professional name). You could use the Slip name if you prefer. I've used boat so we can output a useful message at the end - identifying which boats are a problem so you can easily spot the rows to fix if this is used across sheets or on a dashboard.

The formula checks the Arrival Wet Slip column to see if it matches the Arrival Wet Slip for the current row (this is just like your helper column).

It then looks at the Scheduled Arrival Date column and looks for any that are before or on the current row's Scheduled Arrival Date. These are potential conflicts.

It then looks at the Scheduled Haul Date column and checks if there are any haul dates that are after or on the current row's Scheduled Arrival Date.

Step 2

Wrap the COLLECT in a COUNT function.

That way we can see how many rows meet the criteria above.

=COUNT(COLLECT(Boat:Boat, [Arrival Wet Slip]:[Arrival Wet Slip], [Arrival Wet Slip]@row, [Scheduled Arrival Date]:[Scheduled Arrival Date], <=[Scheduled Arrival Date]@row, [Scheduled Haul Date]:[Scheduled Haul Date], >=[Scheduled Arrival Date]@row))

The results look like this:

Where we have a number greater than 1, we have a conflict.

And what I like, is that it only reports a problem on the second boat. So, if needed, you can do some math on this.

Step 3

Step 2 could be enough, but you could also wrap the formula in an IF function to make it clear what the results show.

This IF says if the COUNT from step 2 is greater than one, return the text "We have a conflict":

=IF(COUNT(COLLECT(Boat:Boat, [Arrival Wet Slip]:[Arrival Wet Slip], [Arrival Wet Slip]@row, [Scheduled Haul Date]:[Scheduled Haul Date], >=[Scheduled Arrival Date]@row, [Scheduled Arrival Date]:[Scheduled Arrival Date], <=[Scheduled Arrival Date]@row)) > 1, "We have a conflict")

The result looks like this:

Step 4

If you want to take this further we can replace the generic "We have a conflict" message with the name of the boat and/or slip using a JOIN and the same COLLECT as in step 1 to return the boat names with space & space between them.

At the end we can add the text " are sharing " and then the wet slip name from the column.

=IF(COUNT(COLLECT(Boat:Boat, [Arrival Wet Slip]:[Arrival Wet Slip], [Arrival Wet Slip]@row, [Scheduled Haul Date]:[Scheduled Haul Date], >=[Scheduled Arrival Date]@row, [Scheduled Arrival Date]:[Scheduled Arrival Date], <=[Scheduled Arrival Date]@row)) > 1, JOIN(COLLECT(Boat:Boat, [Arrival Wet Slip]:[Arrival Wet Slip], [Arrival Wet Slip]@row, [Scheduled Haul Date]:[Scheduled Haul Date], >=[Scheduled Arrival Date]@row, [Scheduled Arrival Date]:[Scheduled Arrival Date], <=[Scheduled Arrival Date]@row), " & ") + " are sharing " + [Arrival Wet Slip]@row)

This is probably too far, but I spent so long thinking about and testing the COLLECT logic that I needed a good payoff!

I hope those formula make sense and the logic achieves what you want to do. Let me know if you have any questions.

Erik D

@KPH, This is an incredible reply and your proposed solution is exactly what I had in my head, but could not make happen. Thank you so much for a well explained and thorough answer.

KPH

Happy to help @Erik D ! I hope I thought of all the possibilities 😬

Let me know if you run into any problems with it.

Erik D

@KPH, With your help, I've made a lot of progress with my application. I'm running into a new problem implementing conditional formatting to highlight conflicts. As you stated above "And what I like, is that it only reports a problem on the second boat."; however, this makes locating the conflicting boat somewhat tedious (scrolling or ctrl+f to find the associated row)". This is because the second boat doesn't "detect" the conflict and show the corresponding error message.

See this example below where Sandbanks and Contigo are sharing H-6 between 2/19-2/20, but Contigo's H-6 is not highlighted red as the error detection column is not showing a conflict.

Here is the column formula for column "Hard Slip Conflict Reporter":

=IF([Hard Slip]@row = "", "Slip Assignment Needed", IF([Hard Slip]@row = "-", "Slip Not Needed", IF(COUNT(COLLECT([Boat Name]:[Boat Name], [Hard Slip]:[Hard Slip], [Hard Slip]@row, [Scheduled Launch Date]:[Scheduled Launch Date], >=[Scheduled Haul Date]@row, [Scheduled Haul Date]:[Scheduled Haul Date], <=[Scheduled Haul Date]@row)) > 1, JOIN(COLLECT([Boat Name]:[Boat Name], [Hard Slip]:[Hard Slip], [Hard Slip]@row, [Scheduled Launch Date]:[Scheduled Launch Date], >=[Scheduled Haul Date]@row, [Scheduled Haul Date]:[Scheduled Haul Date], <=[Scheduled Haul Date]@row), " & ") + " are sharing slip " + [Hard Slip]@row, "No Slip Conflict")))

I'm wondering if it's possible to have both conflicting boats flag a conflict. Alternatively, can you think of a better way to identify conflicting boats (e.g. conditional formatting where if the conflict reporter columns contain [Boat Name] @row then highlight that column red)?

KPH

Hi Erik

I understand what you need. I have a couple of deadlines to meet right now but will have a think and get back to you.

KPH

Hi Erik

I haven't been able to create a list that includes the first boat to arrive, without duplicating the boat names. I might be able to if I think some more. But, I have been able to check a box if there is a conflict. You could use this in your conditional formatting. I actually made mine a little red flag to keep with the theme, but you can use any symbol or just the number 1.

This is the formula:

=IF(OR(COUNT(COLLECT(Boat:Boat, [Hard Slip]:[Hard Slip], [Hard Slip]@row, [Scheduled Arrival Date]:[Scheduled Arrival Date], >=[Scheduled Arrival Date]@row, [Scheduled Arrival Date]:[Scheduled Arrival Date], <=[Scheduled Haul Date]@row)) > 1, COUNT(COLLECT(Boat:Boat, [Hard Slip]:[Hard Slip], [Hard Slip]@row, [Scheduled Arrival Date]:[Scheduled Arrival Date], <=[Scheduled Arrival Date]@row, [Scheduled Haul Date]:[Scheduled Haul Date], >=[Scheduled Arrival Date]@row)) > 1), 1)

It is the original COUNT we used last week to count the number of boats arriving on the same day or before the current boat that have not yet been hauled at the time the current boat arrives. If this is greater than 1, the result is 1 (flag). Then there is a second COUNT that counts the number of boats arriving after the current boat arrives and before the current boat is hauled. If this is greater than 1, the result is also 1 (flag).

Take a look and let me know what you think.