MIN(INDEX(COLLECT on a Multi-Select column

ohara.longevity

I'm hoping I can explain this so it makes sense.

We have a Requisition Tracker that tracks the needs for new hires in new buildings we are implementing.

Often the employee can be used in more than one building.

The building Go Live Dates can differ - the one that is important is the earliest one.

Is there any magic out there that can look at the list in the multi-select - grab the Go Live Date and then select the earliest one?

I need to do this without any premium apps - so bridge magic won't help me here.

Would using a helper column to load the dates work?

I've done things with multi-select - like break them out into multiple helper columns - but I can have upwards of 30 buildings in that list if a position could work across a whole state.

Thank you for any help/insight!

Isa

Paul Newcome

You would first need a column in the reference sheet that has the full matching string. So instead of just "B-0067", you would need whatever you have in the first screenshot that would match the full string. Then you would

=MIN(COLLECT({Dates Column}, {String Column}, HAS([String Column]@row, @cell)))

Paul Newcome

Happy to help. 👍️

Paul Newcome

Are the dates the multi-select column? Are you able to provide some screenshots for context?

ohara.longevity

@Paul Newcome - no the buildings and dates are a one-to-one relationship.

The first screen shot - I need to pull apart the items in the multi-select, and then look-up the dates from the second sheet and pick the earliest date.

Paul Newcome

You would first need a column in the reference sheet that has the full matching string. So instead of just "B-0067", you would need whatever you have in the first screenshot that would match the full string. Then you would

=MIN(COLLECT({Dates Column}, {String Column}, HAS([String Column]@row, @cell)))

ohara.longevity

@Paul Newcome

DANG!! That worked perfectly! Thank you!

I didn't realize that SS would treat the multi-select as individuals with the HAS function… this is life-changing.

Paul Newcome

Happy to help. 👍️