1

I'm building a Google Sheet table and I am stuck on a specific query I want to make

Source Table pic here

I need to sort my Names by "Type" and by "ValueType". I managed to sort them by "Type" with ease, but i'm stuck on the sort by "ValueType" part, because they are in columns, not lines and I can't manage to find a way to sort them by columns

My Query looks like this right now

=QUERY(A1:G8; "SELECT * WHERE A='Type1'; 1)

I want it to look something like this :

=QUERY(A1:G8; "SELECT * WHERE A='Type1' AND C1:G1='ValueType 1'"; 1)

Is it possible to do something like this, and if so, can you please tell me what is the syntax?

Thanks in advance.

Aerials
  • 4,231
  • 1
  • 16
  • 20
Lyter
  • 19
  • 1
  • 3
  • 1
    Please share a copy of your sheet, and ideally a table showing the end result you want to achieve. – Aerials May 27 '21 at 14:09

2 Answers2

1

=QUERY(A1:G8; "SELECT * WHERE A='Type1' AND (C='"&C$1&"' or D='"&D$1&"') "; 1)

adding or X='"&X$1&"' within the () where X is the next letter.

'"&C$1&"' is the value in cell C$1 but you can adapt that for a different value on your sheet, or a fixed value using C='xxx'

Aresvik
  • 4,484
  • 1
  • 5
  • 18
  • This formula could work, but i'm going to regularly add new "Values" so i'll need to change my formula each time i add a value, which is going to take some time... – Lyter May 27 '21 at 15:39
0

You need to expand the formula, or you can do it too (if the number of columns is not fixed)

=QUERY({A1:G8}, "SELECT * WHERE Col1='Type1' and (" & "Col"&arrayformula(TEXTJOIN("='ValueType 1' or Col",,column(C:G)))&"='ValueType 1' )", 1)

try just that sentence to understand how it has been built

="Col"&arrayformula(TEXTJOIN("='ValueType 1' or Col",,column(C:G)))&"='ValueType 1'"
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20