Google Sheets – Show All Duplicate Results with Index() Match() Join()

google sheets

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

Best Answer

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)))&")$")))

Formula

=QUERY(Sheet1!A1:C9;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A PIVOT C")

then the table will appear as you want.

Explained

Here an explanation of the formula, from the inside to the outside:

the PIVOT C transposes the unique results from column C (turning rows into columns)
the GROUP BY A only filters out unique values in column A. (the same as sheet TABLE!A2)
the WHERE B IS NOT NULL ignores empty cells
the SUM(B) adds the result of the query
the SELECT A simply displays column A, as unique values

If you're only interested in individual results, then I suggest using the following formula:

=IFERROR(SUM(FILTER(DATA!$B$2:B;DATA!$C$2:C=$B$1;DATA!$A$2:A=A2));"")

Here an explanation of the formula, from the inside to the outside:

the FILTER function will retrieve the amounts for range B2:B, column B without the header, by filtering range B2:B for "No" and range C2:C for "Yes".
the SUM function will add them together.
the IFERROR will leave a blank cell if an error occurred (not result).

Screenshot

Example

See example file I've prepared, where both samples are present: SUM data based on two variables

Note

The formula in the example file is a bit different (the range is set to contain complete columns).

Google Sheets – Query Language with Regex Matching

To handle word boundaries, you need where A matches instead of where A contains. It allows the use of regular expressions such as .*\bdrew\b.*, meaning

containing the string "drew"
with word boundaries \b left and right of the string (i.e., the characters adjacent to it cannot be word characters)
with any other characters elsewhere in the string: .*

The last bullet item is needed because the logic of matches requires the entire content of A to match the given expression (unlike regexmatch function, which will attempt to match substrings).

So, in your case it would be

=query(A:B, "select * where A matches '.*\bdrew\b.*'")

or, if you want to also match "Drew" (case insensitive), then

=query(A:B, "select * where lower(A) matches '.*\bdrew\b.*'")