Add one field in a queried Field

Question

I have to work with a weirdly formatted dataset.

I have a sheet where lines represent different parts of a model (bike)

The first columns are checkboxes to indicate witch models the part is in. So my handlebars are used in two bikes but each model has it's own wheels

As so :

•     model1 model2 model3   part    price
  

•     x       x             handlebar   2
  

•                     x     handlebar   3
  

•     x                     big wheel   5
  

•             x       x     small wheel 5
  

•             x       x     saddle      7
  

•     x                     Bigsaddle   9
  

How could I manage to get a dataset that would take the same data but add a line for every part I need to order as so :

•     model        part    price
  

•     model1     handlebar   2
  

•     model2     handlebar   2
  

•     model3     handlebar   3
  

•     model1     big wheel   5
  

•     model2     small wheel 5
  

•     model3     small wheel 5
  

•     model2     saddle      7
  

•     model3     saddle      7
  

•     model1     Bigsaddle   9
  

I have tried filtering my dataset or using a query with or logic to get multiple lines only I get something like this :

•     part    price
  

•     handlebar   2
  

•     handlebar   2
  

•     handlebar   3
  

•     big wheel   5
  

•     small wheel 5
  

•     small wheel 5
  

•     saddle      7
  

•     saddle      7
  

•     Bigsaddle   9
  

I'm looking for a way to filter or query while adding the filter column (model1, model2...) in a the first column of my new dataset.

Thanks for any of your help.

Answer 1

Let's assume your original data is in A:E (with headers in A1:E1). Place the following into an open range anywhere to the right of that:

=ArrayFormula({"model", "part", "price";QUERY(SPLIT(FLATTEN(FILTER(IF(A2:C="","##",A1:C1)&"~"&D2:D&"~"&E2:E,D2:D<>"")),"~"),"Select * WHERE NOT Col1 CONTAINS '##' ")})

Better yet, add the original-data sheet-name reference in front of each range within the formula and place the formula in cell A1 of a new, blank sheet.

This one formula will produce all headers and results.

Headers are created first, inside a virtual array contained within curly brackets { }. Below that (as indicated by the semicolon), the results will be placed.

In cases where there is an "x" in the main grid, it just concatenated the left-side labels with the model headers with the parts with the quantities, using a delineator (the tilde symbol) between each. In cases where the main grid is blank, a placeholder of "##" is substituted in place of the left-side label.

These results are FLATTENed into a single column.

QUERY weeds out all rows where "##" exists.

Finally, the remaining results are SPLIT at the tilde to form the columns.