I need your help to create a list of search results.
I have a list of search items (over 10.000 items ) as you can see on screenshot column B.
I wrote a function to bring result from main list (on Sheet DM1). =QUERY('DM1'!$C$4:$J$12874,"select * where G LIKE '%"&B3&"%' ",1)
It works fine. But I need to add all the search result (which always include more than one row mostly 4-5 rows and consist of 8 columns) after each other without empty cells between.
So basically according to my search results I would like to make (continuous) another complete list which consist of 8 columns.
How can I do that on google sheets?
If there is a way to filter my main list on DM1 sheet with the values from my search list in another way that works for me also.
Column B Search list, Column C to J is the results
@ale13 hey, The screenshot shows search result for only "14Q02". And following items on the list (column B) needs to be copied right under it.
My formula copies with titles { I do not need that after first search item so formula could be =QUERY('DM1'!$C$4:$J$12874,"select * where G LIKE '%"&B3&"%' ",0) }
Here is the screenshot (I only added second item search result which is "14Q04".)
I have two possible approaches, depending on how many listed tags you might have in column B of Sheet1. If it is only going to be up to ten or so, you could do something like this:
=QUERY(DM1!C4:J12,"select * where G <> '' and (G = '"&B3&"' OR G = '"&B4&"' OR G = '"&B5&"' OR G = '"&B6&"' OR G = '"&B7&"' OR G = '"&B8&"' OR G = '"&B9&"') ",1)
The second approach, perhaps more elegant, is building the query dynamically like this:
=QUERY('DM1'!$C$4:$J$12,"select * where G <> ''" & CONCATENATE(A3:A99),1)
Then in A3 (or in some other hidden helper column if preferred) you place this formula:
={"AND G='" & B3 & "'";ArrayFormula(if(len(B4:B),"OR G = '"&B4:B&"' ",""))}
This ends up with the same formula as earlier, but it dynamically adds the following text to the QUERY for each listed tag in column B.
OR G = 'tag-value' .
I've added a sample tab, Sheet1-GK, on your sheet to demonstrate this.
Let me know if this works for you.