google sheets
I want to be able to sort rows of cells with differing data based on whether or not any one of the cells in each row has a problem/is incomplete.
EXAMPLE:
If I have hundreds of columns that all have different names in column A, and 5 cells in each row have random 1s and 0s, how do I filter out all of the rows that happen to contain 0s in them? Or all 1s? Etc.
Please feel free to ask me to elaborate if need be.
There is not normally any difference between sorting on values as such or sorting on the results of formulae. Column A shows the result of applying weekday to the chronologically entered values in ColumnB. D:E is a copy of A:B but sorted on Column D ascending.
There may be issues however with certain formulae that use the likes of Row() or offset, in which case it would help if your Q specified or gave an example of such formulae.
Use a custom function.
function result(range) {
var splitCol = 1; // split on column B
var output2 = [];
for(var i=0, iLen=range.length; i<iLen; i++) {
var s = range[i][splitCol].split("\n");
for(var j=0, jLen=s.length; j<jLen; j++) {
var output1 = [];
for(var k=0, kLen=range[0].length; k<kLen; k++) {
if(k == splitCol) {
output1.push(s[j]);
} else {
output1.push(range[i][k]);
}
}
output2.push(output1);
}
}
return output2;
}
The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.
data
result
I've created an example file for you: multi-line cells into new rows.
Best Answer
Do you mean, different names in A, and B to F of each row there are 1s or 0s randomly? And, you want to extract/filter the names with all 0s in B to F or all 1s?
I expect this is similar to what you said. I hope this is helpful. 1 Line for a copy & paste.
P.S. Oh, The text "A34" in B31 should be "A32".