I'm using Google Sheets (only option, sorry, know this should be a database).
Sheet 1: List of 200 employee names, and up to 10 location codes they are frequently assigned to.
Sheet 2: List of 50 organization, each with 9 location codes.
What I want to do is have a way of figuring out which employees could be sent to organization #22, based on if there is any overlap of them both being assigned the any of the same codes.
So, employee #189 might have codes: D123, D239, D873, D982, T893, T762, T874
And on the other sheet organization #22 has codes: D1, D2, D3, D120, D121, D123, T1, T2, T3
They should be a "match" because the employee has the code D123 – which is one of the parameters assigned to organization #22.
Is this possible??
Google spreadsheets will support some query functions but this is really beyond my normal usage.
Best Answer
Assuming the location codes are comma-space separated in column B of each sheet.
My approach would be first to extract the location codes for the relevant organisation:
=VLOOKUP("#22",'Sheet 2'!A2:B,2,0)
Then manipulate that to produce a regex-type string that can be used in a QUERY:
="(.+, |^)"®EXREPLACE(VLOOKUP("#22",'Sheet 2'!A2:B,2,0),", ","(, .+|$)|(.+, |^)")&"(, .+|$)"
So the result of that would be something like:
(.+, |^)D1(, .+|$)|(.+, |^)D2(, .+|$)|(.+, |^)D3(, .+|$)|(.+, |^)D120(, .+|$)|(.+, |^)D121(, .+|$)|(.+, |^)D123(, .+|$)|(.+, |^)T1(, .+|$)|(.+, |^)T2(, .+|$)|(.+, |^)T3(, .+|$)
That kludge should ensure that, for example, D1 will not be found inside D123.
Then that can be plugged into a QUERY select clause using the "matches" operator, which supports regex. So the final formula would be:
=QUERY('Sheet 1'!A2:B,"select A where B matches '(.+, |^)"®EXREPLACE(VLOOKUP("#22",'Sheet 2'!A2:B,2,0),", ","(, .+|$)|(.+, |^)")&"(, .+|$)'",0)