How can I combine multiple columns into one column in Google Sheets?

1
votes

(Note: Please simply look at the Google sheet for the quickest understanding of what I'm describing in the below bulletpoints)

  • My data has rows which each represent an order
  • Each order (row) can consist of multiple products
  • For each product in an order (row) there is another set of columns in the same row
  • I need this data to convert into only one set of columns per row (i.e. one product per row)
  • The products (new rows) need to remain next to eachother so the columns can't just be added to the bottom of the array (which is more simple)

Can you please take a look at the example below and help me achieve this?

Example Sheet

Screenshot of linked sheet

1
google-sheetsgoogle-sheets-formulaspreadsheet

1 Answers

1
votes

Try this in another sheet

=SORT({query({Reference!$A5:$A,Reference!B5:F},"select * where Col2 is not null ");query({Reference!$A5:$A,Reference!G5:K},"select * where Col2 is not null ");query({Reference!$A5:$A,Reference!L5:P},"select * where Col2 is not null ")})