Multi-select string operation

Amanda0220

Hi all,

I have three columns which have the same drop down options: requested, received and pending. I would like the "pending" column to have all options from the "requested" column except for what's in the "received" column.

For example: under "requested": option 1,2,3,4; under "received": option 1,2; under "pending" automaticlly pop up option 3,4.

Basiclly I want to do a minus operation between "requested" and "received" and the results are also in multi-select form.

Thanks!

Genevieve P.

Hi @Amanda0220

I'm not quite sure I understand your set-up, but I've built a tester sheet to try and replicate what you described. Essentially, it sounds like you want the value of "Pending" to appear, but only if there is content in the original Requested column and there is not the selection of "Received" in the Received column, is that correct?

If so, you can use an IF statement with an AND function to look for those two criteria:

=IF(AND(Requested@row <> "", Received@row <> "Received"), "Pending", "")

<> means "not", so I'm looking to see if the Requested column is not blank, and that the Received column is not "Received". Otherwise, it will return blank:

You may want to make this a column formula (see here) so that no one tries to manually select something else, which would break the formula.

Let me know if I've misunderstood your set up or if I can help further! It would be useful to see screen captures of your sheet if you need more help, but please block out any sensitive data.

Cheers,

Genevieve

Amanda0220

Hi @Genevieve P

Thanks for your detailed reply, which gives me an idea to think about it differently. I appreciate it!

I add a screenshot below hope to make myself clear. For each row, the "Requested" and "Received" cells are manually entered by users, and I would like the "Pending" cell to automatically appear what's in the "Requested" minus what's in the "Received" if that's possible.

Kelly Moore

Hi @Amanda0220

Here is a possible solution using the substitute formula. The formula first checks to see if the count of terms in the Requested and Received columns are equal- it's not checking if the contents are the same. If the two cells are equal in count, it enters Received in your Pending column. Without that check, the Pending cell would end up blank if the two columns equaled each other - which might be ok? Omit the IF-statement if a blank cell doesn't matter.

The Substitute function searches the Requested@row for the Received@row text. When it finds that text, it replaces that text with a blank, - which essentially gives you a Requested - Received outcome.

=IF(COUNTM(Requested@row) <> COUNTM(Received@row), SUBSTITUTE(Requested@row, Received@row, ""), "Received")

No IF statement

=SUBSTITUTE(Requested@row, Received@row, "")

Hope this helps