Google Sheets - Filtering out values in a range from another range

0
votes

-- EDIT #2 -- Updated the Google Sheet again with a solution which is painfully close. Best formula I've had so far is below. --

=ARRAYFORMULA(SPLIT(UNIQUE({ARRAYFORMULA(QUERY(IF(COUNTIF(G4:G&"|||"&H4:H,ARRAYFORMULA(A4:A&"|||"&B4:B))>0,REGEXREPLACE(A4:A&"|||"&B4:B,".*",""),A4:A&"|||"&B4:B),"SELECT * WHERE Col1 IS NOT NULL"));ARRAYFORMULA(QUERY(IF(COUNTIF(G4:G&"|||"&H4:H,ARRAYFORMULA(D4:D&"|||"&E4:E))>0,REGEXREPLACE(D4:D&"|||"&E4:E,".*",""),D4:D&"|||"&E4:E),"SELECT * WHERE Col1 IS NOT NULL"))}),"|||"))

-- EDIT -- Updated the Google Sheet to more closely reflect my use case --

Pretty confident someone's asked this before but I've been Googling for a few hours now and I'm starting to lose hair. I think I've got to use a QUERY function but not 100% on that.

Demo sheet here: https://docs.google.com/spreadsheets/d/1p_hqk9WydcyXQZT4bIm4DSZZnaPKbngtnZ0-laYHwk8/edit?usp=sharing

What I want to do: I want to combine the ranges under DATA 1 and DATA 2, but I want to exclude and rows which start with the values in DATA 3.

RESULT 1 doesn't add value but shows how I was adding DATA 1 and DATA 2 together.

RESULT 2 shows the result I'm trying to get.

RESULT 3 hidden but where I got to (and doesn't add value again, sorry). I can get it mostly working, but I'd have to manually specify in the QUERY which combinations I'm looking for... and frankly my dataset is HUGE. That formula currently looks like this:

=QUERY(UNIQUE({FILTER(A4:B,NOT(ISBLANK(A4:A)));FILTER(D4:E,NOT(ISBLANK(D4:D)))}),"SELECT * WHERE NOT Col1 STARTS WITH 'a' OR NOT Col2 STARTS WITH 'v'",-1)

Hope someone can help me out! You're my only hope.

1
google-sheetsgoogle-sheets-formulagoogle-sheets-query
Would a custom function written in Apps Script be a suitable solution for you? – I hope this is helpful to you
I'm always keen to stick to formulas where possible, but if it makes a solution far easier I'm more than happy to go with Apps Script. Feel pretty comfortable with it generally speaking. – PuGZoR
I've got a partial solution but it's not quite there. The wildcards within my COUNTIF isn't working well though. Has anyone got a solution for this? =ARRAYFORMULA(SPLIT(UNIQUE({ARRAYFORMULA(QUERY(IF(COUNTIF(G4:G&"|||"&H4:H,ARRAYFORMULA(A4:A&"|||"&B4:B))>0,REGEXREPLACE(A4:A&"|||"&B4:B,".*",""),A4:A&"|||"&B4:B),"SELECT * WHERE Col1 IS NOT NULL"));ARRAYFORMULA(QUERY(IF(COUNTIF(G4:G&"|||"&H4:H,ARRAYFORMULA(D4:D&"|||"&E4:E))>0,REGEXREPLACE(D4:D&"|||"&E4:E,".*",""),D4:D&"|||"&E4:E),"SELECT * WHERE Col1 IS NOT NULL"))}),"|||")) – PuGZoR

1 Answers

1
votes

This works for your example data. It uses ARRAYFORMULA and MATCH to concatenate the two columns and LEFT to only use the first character in the match function. You might need to find a slightly smarter way to do the starts with element depending on your actual data.

=UNIQUE({FILTER(A4:B,not(isblank(A4:A)),iserror(MATCH(ARRAYFORMULA(A4:A&left(B4:B,1)),ARRAYFORMULA(G4:G&H4:H),0)));FILTER(D4:E,not(isblank(D4:D)),iserror(MATCH(ARRAYFORMULA(D4:D&left(E4:E,1)),ARRAYFORMULA(G4:G&H4:H),0)))})

Documentation:

MATCH: here

ARRAYFORMULA: here