Hello fellow Stack Overflowers! First of all - I cannot share the file I'm working on since it contains confidential company data. I'm building a raport/file that will assist my team in ordering spare parts. In order to do that, I've decided to create a query builder which will output what I want based on conditions that are inputted: enter image description here From top to bottom: Filtruj po dostawcy - Filter by supplier - Select which supplier You'd like to have outputted (or all of them), Sortowanie- Sorting column - Select a column by which the output will be sorted Opcje sortowania - Sorting options - context-sensitive, gives You more options to sort (e.g. sort by estimated stock based on 6 or 24 month history) Kierunek sortowania - Sorting type - ascending or descending Uwzględnij pola "b/d" z 24 mies - Include "N/A" results - select whether You'd like to see all parts, or only those for which we have data to calculate estimated stock

In order to do that, I'v created a query, with internal conditions, which construcs it based on the above conditions: query(Import!A2:N;"select A,F,G,I,J,K where A is not null "&JEŻELI(B1="WSZYSTKO";"";"and D matches '"&B1&"'") &JEŻELI(B5="NIE";" and not K matches 'b/d'";"") &ifs(B2="Alfabetycznie";" order by A";B3="Bez zamienników";" order by F";B3="Z zamiennikami";" order by G";B3="6 mscy";" order by I";B3="12 mscy";" order by J";B3="24 msce";" order by K") &JEŻELI(B4="Malejąco";" desc";" asc")) As You can see, it has a input range specified, output columns, prefilter to exclude null results, and then constructs the query. The problem is that sorting is all wrong. When I sort alphabetically by part number, all dandy, but when I try to sort by numerical values, it behaves like it would be sorting alphabatically by those values: enter image description here

I've tried to simply format them as numbers - no change. Forcing the input data to be numbers (by using TO_VALUE function) is no go. Maybe the problem is that some values are strings ("b/d" - which basically means that I have no data to show and this is a placeholder)?

My dream result is... the query outputting correctly sorted data.

CodePudding user response：

The behavior you describe tells that the values are not numbers but text strings that look like numbers. Try Insert > Sheet, name the new sheet Import2, and put this formula in cell A1 of the new sheet:

=arrayformula( 
  if( 
    len(Import!A2:A), 
    iferror( 
      value( 1 / Import!A2:N ^ -1 ), 
      Import!A2:N 
    ), 
    iferror(1/0) 
  ) 
)

Then format the data in the new sheet the way you want, and replace Import!A2:N in your query() formula with Import2!A2:N.

CodePudding user response：

And if you leave the sorting part outside of the query? I think you'd be able to overcome the given limitations. Try implementing your conditionals about order but as column numbers of SORT and 1 or 0 for ascending or descending. Sorry I don't try to do it myself but I'm afraid of mixing up because of language limitations, if you give access to a spreadsheet I may be able to help more

SORT

Syntax

SORT(put here your query, sort_column-numbers 1 to 6-, is_ascending-1 for asc and 0 for desc-, [sort_column2, is_ascending2, ...])