2

I'm trying to write a formula to make volunteer-task scheduling easier. In the database I have lists with volunteer names that have chosen specific tasks and specific timeslots.

I wrote a formula to match tasks and timeslots and show me all matching names in the same cell, joined by "," as a separator. The cells would show, for example, all volunteers that have chosen timeslot1 and task5.

The results of these will be table crossing all tasks and timeslots.

My problem is: I want to compare two INDEX()MATCH() results and show only and all of the duplicate results. Right now it is only showing the results that are in the same order. Meaning: If Sara is result #1 after the first INDEX()MATCH(), it will only be shown if it is result #1 after the second INDEX()MATCH().

Here is the formula:

=join(", ", filter(index($C$22:$H$26, match(E9, $B$22:$B$26)),index($C$22:$H$26, match(E9, $B$22:$B$26))=index($C$17:$H$21, match(D10, $B$17:$B$21))))

Here is the link to the document. I tried to explain further there:
https://docs.google.com/spreadsheets/d/1qjDfqpOMeJEelqRGFOwk5dKuzB7pB14OY_g0cjljrB4/edit#gid=0

2

I would filter based on REGEXMATCH. The regular expression would be the join of one of two rows. Suppose the names are in rows 4 and 5, and columns E through J. I'm using row 5 to make a regular expression, joining the names with "or" symbol |:

="^("&JOIN("|", FILTER(E5:J5, LEN(E5:J5)))&")$" 

This returns "Lars|Anairis|Emil|Johan|Leela". Note I'm filtering out any blank cells to avoid getting || in regular expression, which would match anything. Also, the ^ and $ symbols ensure that only the entire string will match.

Then filter row 4 by the above regular expression (replaced with ... for readability):

=JOIN(", ", FILTER(E4:J4, REGEXMATCH(E4:J4, ... )))

This returns "Leela, Anairis, Lars", which is the desired result.

When put together, the whole formula is somewhat scary:

=JOIN(", ", FILTER(E4:J4, REGEXMATCH(E4:J4, "^("&JOIN("|", FILTER(E5:J5, LEN(E5:J5)))&")$"))) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.