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.

1 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.

  • Ok, so it toke me some time to wrap my head around that one, but it definitely works ! Thank you Erik ! Commented Nov 17, 2021 at 9:35
  • Just for further reference the french version that works in my office =ArrayFormula({"model"\ "part"\ "price";QUERY(SPLIT(FLATTEN(FILTER(SI(A2:C="";"##";A1:C1)&"~"&D2:D&"~"&E2:E;D2:D<>""));"~");"Select * WHERE NOT Col1 CONTAINS '##' ")}) Commented Nov 17, 2021 at 9:38
  • Yes, some international locales use a semicolon where the US uses a comma, and a backslash where the US uses a comma. I'm glad that you were able to adapt the formula to your locale. In future posts, you can always mention your locale in the post itself, which will typically result in formulas being written for your locale from the start.
    – Erik Tyler
    Commented Nov 17, 2021 at 16:46
  • If you would, please take a moment to mark my post as "Best Answer." This will signal to the larger contributor community that the issue has been fully addressed; and it will help future site visitors most quickly find this answer if it applies to their own similar issue.
    – Erik Tyler
    Commented Nov 17, 2021 at 16:47
  • I would only i have no reputation... : Thanks for the feedback! You need at least 15 reputation to cast a vote, but your feedback has been recorded. Commented Nov 19, 2021 at 7:44

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.