4

My google sheet is importing data from another tab just for sorting purpose, however, when the sort is done manually (through Google Sheet menus), results are unpredictable.

The sheet is populated through:

=query(tabname!A4:G2000)
0

3 Answers 3

5

you can't use Data > Sort sheet by...

your sheet needs to be manually sorted by creating a filtered view by selecting your column and then: Data > Filter views... > Create new filter view > Sort A - Z

a downside is that it's a view so it won't stay like that after closing your spreadsheet.
also, note that formula needs to be outside or inside of the filtered area (eg. filter can't be created right on the formula)

EXAMPLE:


another approach would be to use:

  • =QUERY() in =SORT() like: =SORT(QUERY(tabname!A4:G2000); 1; 1)
  • =QUERY(tabname!A4:G2000; "select * order by A desc, B asc"; 1)

5
  • Same result...........
    – Riccardo
    Commented Sep 6, 2018 at 15:17
  • @Riccardo can you share a copy of your sheet?
    – user0
    Commented Sep 6, 2018 at 15:19
  • Yes, here you go: docs.google.com/spreadsheets/d/…
    – Riccardo
    Commented Sep 7, 2018 at 7:00
  • @Riccardo I see now why its not working for you... you are attempting to create filter right on the formula
    – user0
    Commented Sep 7, 2018 at 7:57
  • @Riccardo I've updated my answer. see example
    – user0
    Commented Sep 7, 2018 at 8:37
2
  • Keep your primary sheet as a main "level 1" sorting logic and querying sheet.

  • Use ORDER BY in your first sheet.

  • Hide your first sheet.

  • Then pull your data to a second sheet with a simple ARRAYFORMULA.

Now you can filter (previously sorted data) manually through Google Sheet menus in a user-friendly way.

0

This problem occurs because the way that works functions like QUERY, IMPORTRANGE, among others, that returns an array of values instead of single values. While we see each value on different cells, they actually are "tied" together.

To avoid this problem, we should not sort manually the results of formula because, as the OP already found, "results are unpredictable".

If you are using QUERY you could use ORDER BY clause to sort values. Another alternative that keeps the reference to the source range is to use nest QUERY inside a SORT function.

In some cases, doing a copy / paste / manipulate data and repeat could be the better approach.

1
  • This is a big issue for me as the query-populated sheet should be sortable by end user using the classic manual sort
    – Riccardo
    Commented Sep 7, 2018 at 7:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.