How to query select multiple columns if columns <>'' and Col1 contains “string”

0
votes

Import From Sheet: https://docs.google.com/spreadsheets/d/1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw/edit?usp=sharing

Import To Sheet: https://docs.google.com/spreadsheets/d/1n6gHMv_R8sipeY7PbLbgLYde2o66NM-v-_KL8k_mmug/edit?usp=sharing

Import From Sheet: enter image description here

Import To Sheet: enter image description here

I'm trying to Select Multiple cells imported into 1 column in order if this criteria is met:

Col1 = Maintenance:
Col3 is not <>''
Col12 is not <>''
Col21 is not <>''
Col30 is not <>''

Having some trouble removing the blank cells with my query though.

=TRANSPOSE(QUERY({Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 1-4'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 5-8'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 9-12'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 13-16'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 17-20'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 21-24'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 25-28'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 29-32'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 33-36'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 37-40'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 41-44'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 45-48'!B97:AL162");
Importrange("1-CDuKq2ueygE8qLdV6EH1eeXT9NSYYG8N_k4XqDEtdw","'Week 49-52'!B97:AL162")},
"SELECT Col3, Col12, Col21, Col30 where Col1 contains 'Maintenance:' and Col3, Col12, Col21, Col30 is not <>''"))
1
google-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-languageimportrange
Did Player0 answer solved your problem? Could you provide a sample of what you are trying to do? Maybe what you are trying to achieve is very specific for your case and a more generic example could be more helpful. – Raserhin
Unfortunately I still haven't gotten it to work, I provided links to sample sheets. – Peter Chabot

1 Answers

1
votes

you need to do it like this:

=TRANSPOSE(QUERY({
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 1-4'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 5-8'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 9-12'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 13-16'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 17-20'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 21-24'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 25-28'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 29-32'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 33-36'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 37-40'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 41-44'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 45-48'!B97:AL162");
Importrange("1Pf4NwDDpKnRcn16SPNwpIJsJZMB0ISfOtpsaIkaiZwM","'Week 49-52'!B97:AL162")},
"select Col3,Col12,Col21,Col30 
 where Col1 contains 'Maintenance:' 
   and Col3 is not null
   and Col12 is not null
   and Col21 is not null
   and Col30 is not null"))

or use or instead of and if needed

0