google sheets
I have a Google Sheet of data that I would like to organize. There are multiple rows with values sorted by column. I would like to sort rows 3-19 based on the value in column B.
The algorithm I am using is this:
=SORT(3:19, B3:B19, FALSE)
And my error is:
Array result was not expanded because it would overwrite data in B2.
I have looked at this Google Support page but cannot get it to work properly.
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.
No, it's not possible to "skip columns".
Instead you could use two query functions, one to be used to get the columns A - C, the other to get the columns F and following.
First formula (A1):
=QUERY(
IMPORTRANGE("URL","Sheet Name!A2:P"),
"SELECT Col1,Col2,Col3
WHERE Col11 CONTAINS 'West'",
1
)
Second formula (F1):
=QUERY(
IMPORTRANGE("URL","Sheet Name!A2:P"),
"SELECT Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15
WHERE Col11 CONTAINS 'West'",
1
)
REMARKS:
Note that instead of Sheet Name!A2:P1000 was used Sheet Name!A2:P as this take the whole columns instead of only 1000 rows.
Best Answer
You are trying to sort the rows in place, with the output of
sortoverwriting the original rows. Spreadsheet formulas can't do that.Instead, put the
sorton another sheet. For example, if the original unsorted data is in Sheet1, the formula will be