Remove all values from a multi-select column if they appear in a different column

Matthew Emrich

I have two columns, one with a list of potential instruments and one with the instruments that aren't available. If I created a third column for instruments available, how would I remove the used instruments using a formula? All are multi-select columns

Mike TV

@Matthew Emrich

Could you possibly provide an image of the sheet? I need to know which of the 3 columns are multi-select drop downs and how you have your sheet formatted.

Matthew Emrich

@Mike TV Photo Added, sorry thought I did.

Matthew Emrich

Mike TV

@Matthew Emrich

Sorry, I need further clarifications to understand those 2 columns in your image.

Is each row separate from the others? I ask because the instrument IDs repeat a lot.

If each row is separate, then would the "Available Instruments" correct result for these two rows be "tox - 1015"?

Why do these two lines have a listed "Instrument in Use" listed when the yellow column is either blank or doesn't have that instrument type listed at all?

Matthew Emrich

@Mike TV

Each column have cross-sheet formulas. That look up equipment that is being reserved. The first column determines what machines are available and has the formula

=JOIN(DISTINCT(COLLECT({CENTRIFUGE #}, {equipment}, INSTRUMENT@row, {CENTRIFUGE Schedule Range 2}, NOT(@cell <= [END DATE]@row), {start}, @cell >= [DATE START]@row)), CHAR(10))

Ideally I would have used this column to bring in the values I want but because the reservation sheet has multiple values for each equipment I was not able to figure ouyt a formula that works

The Column equipment in use pulls out what equipment is reserved during that time period, using the formula

=JOIN(DISTINCT(COLLECT({CENTRIFUGE #}, {equipment}, INSTRUMENT@row, {start}, @cell >= [DATE START]@row, {CENTRIFUGE Schedule Range 2}, @cell <= [END DATE]@row)), CHAR(10))

Mike TV

@Matthew Emrich

Could you possibly manually fill out the Instrument Available column with what the successful outputs would be of the formula you need? Something like this: