CODEWITHSUNDEEP
google-sheets
Matt
Matt

Reputation: 1

GSheets filter by string from multiple items in a list

Good morning,

I am trying to filter one list (Website page URL and views) by multiple strings (unique ID contained in URL) contained in a second list, based on a condition (all pages, specific page or group of pages).

Summary = tab containing condition control (cell F17 - All / Group / specific page), unique ID (cell F18, populated when F17 set to specific page), and page group (G17) DataPageViews = data sheet, column A full URL, column B page views DataList = data sheet containing full URL and specific page slug for lookup (column P) DataGroup = data sheet that dynamically updates based on Group selected on summary page with URL slug contained in D3:D.

I have no problem filtering the list by a single string from a second list (static or dynamic) using the REGEXMATCH:

but cannot get the filter to work on all strings for both the "All" condition or the "Group" condition

I have tried using various permutations of countif(), regexmatch() and arrayformula() but cannot get this to work.

The code looks like this - broken parts replaced with ...!

`=filter(DataPageViews!$A:$B,if(Summary!$F$17=DataList!$A$2,...,if(Summary!$F$17=DataList!$A$3,...,regexmatch(DataPageViews!$A:$A,Summary!$F$18))))```

1st removed part is trying to evaluate for all page URLs that contain all the page slugs from DataList tab (DataLists!P2:P) 2nd removed part is trying to evaluate for all page URLs that contain all the page slugs that appear in DataGroup dynamic list (DataGroup!D3:D)

Thank you!

UPDATED SOLUTION:

(if(F17=DataInnsList!$A$3,
  TEXTJOIN("|", 1, QUERY({DataInnsList!A2:P}, "select Col16 where Col10 = '"&G17&"'",)),
if(F17=DataInnsList!$A$2,
 TEXTJOIN("|", 1, QUERY({DataInnsList!A2:P}, "select Col16 where Col5 != '"&F17&"'")),
if(not(or(F17=DataInnsList!$A$2,F17=DataInnsList!$A$3)),
 QUERY({DataInnsList!A2:P},"select Col16 where Col5 = '"&F17&"'"),"no data")
))),"no data")

Upvotes: 0

Views: 181

Answers (1)

player0
player0

Reputation: 1

try F18:

=IFERROR(TEXTJOIN("|", 1, QUERY({DataInnsList!A2:P}, "select Col16 where 1=1 "&
 IF(F17="",," and Col1  = '"&F17&"'")&
 IF(G17="",," and Col10 = '"&G17&"'"))), "no data")

and for pageviews:

=IFERROR(SUM(1*FILTER(DataPageViews!B:B, REGEXMATCH(DataPageViews!A:A, F18))), "no data")

enter image description here

Upvotes: 1

Related Questions