1

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.

migrated from superuser.com Sep 4 '14 at 10:21

This question came from our site for computer enthusiasts and power users.

1

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:

="(.+, |^)"&REGEXREPLACE(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 '(.+, |^)"&REGEXREPLACE(VLOOKUP("#22",'Sheet 2'!A2:B,2,0),", ","(, .+|$)|(.+, |^)")&"(, .+|$)'",0)

Your Answer

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