google sheetsgoogle-sheets-query
I'm getting some really weird results from my QUERY() Here is an image with an example:
I'm querying for the name where No Show = "No Show". It find the correct name Name 5 but also returns a concatenated list of names separated by a space.
Why is this happening? How do I fix it?
This seems to be easiest solved with a script. If you have a list of philosopher names (column A) and their abbreviations (column B):
Name | Abbreviation
--------------------------
Socrates | soc
Plato | pla
...
in a sheet named Philosophers, you should be able to use the following script function:
function getPhilosophersByAbbreviations(abbreviationsStr) {
var philosophers = SpreadsheetApp.getActive().getSheetByName("Philosophers").getDataRange().getValues();
var result = new Array();
var abbreviations = abbreviationsStr.split(",");
for (var a = 0; a < abbreviations.length; a++) {
var abbreviation = abbreviations[a];
for (var p = 0; p < philosophers.length; p++) {
if (philosophers[p][1] == abbreviation) {
result.push(philosophers[p][0]);
}
}
}
return result;
}
In your data sheet, you enter the formula in column B:
soc | =getPhilosophersByAbbreviations(A1)
pla | =getPhilosophersByAbbreviations(A2)
des | =getPhilosophersByAbbreviations(A3)
heg | =getPhilosophersByAbbreviations(A4)
heg,des | =getPhilosophersByAbbreviations(A5)
... and the full names will be displayed in columns B and so on.
Feel free to play with the example spreadsheet I set up.
This is more dynamic if you also use regex instead of wildcards as the specific way in which you're using the wildcard is different in Google Sheets compared to Excel .... but this works:
=join(", ", filter(Inventory!B:B, istext(regexextract(Inventory!A:A,Master!A2))))
Note: you should also finally wrap your whole function with =iferror() so that you don't have to see that ugly #N/A when there are no matches... secondly, occasionally I convert everything to lowercase inside of the matching portion of the formula so as to avoid any one of many complications that can happen when you user generated content or input.
That would end up looking like this:
=iferror(join(", ", filter(Inventory!B:B, istext(regexextract(arrayformula(lower(Inventory!A:A)),lower(Master!A2))))))
Best Answer
The third, optional, argument of
querycommand isIn your situation, the formatting of column A confused the query, and it interpreted most of that column as header rows. Solution: explicitly indicate the number of header rows, i.e., 1 in your case.