I'm trying to narrow down how to join or combine related data from two different tabs that exist in one google sheet.
Tab 1: groups-users
+-------------+---------+
| group | user |
+-------------+---------+
| Blue | Ana |
| Blue | Jon |
| Blue | Ben |
| Red | Ana |
| Red | Ben |
| Green | Sue |
| Green | Jon |
| Green | Ben |
+-------------+---------+
Tab 2 group-repos
+-------------+---------+
| group | repo |
+-------------+---------+
| Blue | code |
| Blue | script |
| Blue | web |
| Red | code |
| Red | script |
| Red | web |
| Red | form |
| Green | script |
| Green | tools |
+-------------+---------+
What I need to quickly know is Which Repos does Ana have access to?
At a glance from this sample, I see that Ana has access to repos code, script, web, and form because she belongs to groups Blue and Red. And that Sue only has access to repos script and tools because she's only part of group Green. But the actual data I have in front of me is a couple of hundred lines long per Tab.
I've done some web searching, and the QUERY function keeps popping up, but I haven't ran into any examples that addresses this scenario. Any help would be appreciated!
I played around with it in this link.
https://docs.google.com/spreadsheets/d/1ZVnyRklT7iM_AX2Yr39o3G5u-3hAWPM4l_eYbsisyEY/edit#gid=0
You can see in cell G1 is this formula:
=arrayformula(unique(if(regexmatch(join(",",filter(D:D,E:E=F1)),A1:A10),B1:B10,)))
What it does is filter for the specific person (chosen by the dropdown). Then it joins all groups that the person is part of together with commas. The regexmatch searches to see if the values from the second table match the values found in the joined string. If they are found, then I return the repo, otherwise I do nothing. I then have a unique function wrapping around everything just to get rid of any repeat repos.