I currently have a Google Sheet with the following column/row layout:

firstname1, address1, orderdate1, item1, orderdate2, item2, orderdate3, item3
firstname2, address2, orderdate1, item1, orderdate2, item2
firstname3, address3, orderdate1, item1

The desired output I am trying to achieve would look like this:

firstname1, address1, orderdate1, item1
firstname1, address1, orderdate2, item2
firstname1, address1, orderdate3, item3
firstname2, address2, orderdate1, item1
firstname2, address2, orderdate2, item2
firstname3, address3, orderdate1, item1

This is the formula that I was working with:

=let(Σ,tocol(,1), reduce(Σ,indirect("Sheet1!C2:"&index(match(,0/(Sheet1!A:A<>"")))),lambda(a,c,vstack(if(iserror(a&""),Σ,a),if(len(c),hstack(index(Sheet1!A:B,row(c)),c),Σ)))))

however the output was:

firstname1, address1, orderdate1
firstname1, address1, item1
firstname1, address1, orderdate2
firstname1, address1, item2
firstname1, address1, orderdate3
firstname1, address1, item2
firstname2, address2, orderdate1
firstname2, address2, item1
firstname2, address2, orderdate2
firstname2, address2, item2
firstname3, address3, orderdate1
firstname3, address3, item1

I'm not sure how to group the orderdate and item cells together so they remain on one line

I currently have a Google Sheet with the following column/row layout:

firstname1, address1, orderdate1, item1, orderdate2, item2, orderdate3, item3
firstname2, address2, orderdate1, item1, orderdate2, item2
firstname3, address3, orderdate1, item1

The desired output I am trying to achieve would look like this:

firstname1, address1, orderdate1, item1
firstname1, address1, orderdate2, item2
firstname1, address1, orderdate3, item3
firstname2, address2, orderdate1, item1
firstname2, address2, orderdate2, item2
firstname3, address3, orderdate1, item1

This is the formula that I was working with:

=let(Σ,tocol(,1), reduce(Σ,indirect("Sheet1!C2:"&index(match(,0/(Sheet1!A:A<>"")))),lambda(a,c,vstack(if(iserror(a&""),Σ,a),if(len(c),hstack(index(Sheet1!A:B,row(c)),c),Σ)))))

however the output was:

firstname1, address1, orderdate1
firstname1, address1, item1
firstname1, address1, orderdate2
firstname1, address1, item2
firstname1, address1, orderdate3
firstname1, address1, item2
firstname2, address2, orderdate1
firstname2, address2, item1
firstname2, address2, orderdate2
firstname2, address2, item2
firstname3, address3, orderdate1
firstname3, address3, item1

I'm not sure how to group the orderdate and item cells together so they remain on one line

• google-sheets
• macros

2 Answers 2

Try this formula using QUERY and its detailed documentation:

=query(
  {A1:D;A1:B,E1:F;A1:B,G1:H},
  "select * where Col1 is not null and Col3 is not null order by Col1"
)

In this formula, the three groups of columns are stacked:

• {A1:D;A1:B,E1:F;A1:B,G1:H}
  • Col1 and Col2 are always the firstname and address
  • Col 3 is always an order date
  • Col4 is always an item number

The formula is dynamic, it will automatically respond to extra rows of data. The key is:

• where Col1 is not null - will ignore any blank rows
• and Col3 is not null will ignore any rows where there is a blank orderdate
• order by Col1 - will sort in firstname order.

DATA - before

DATA - after

Try this alternative approach

Using Filter() to include only rows with non-empty values for orderdate and item, ensuring that blank rows are excluded from the output.

=SORT(ARRAYFORMULA({
  "firstname", "address", "orderdate", "item";
  FILTER(A2:A, C2:C <> ""), FILTER(B2:B, C2:C <> ""), FILTER(C2:C, C2:C <> ""), FILTER(D2:D, C2:C <> "");
  FILTER(A2:A, E2:E <> ""), FILTER(B2:B, E2:E <> ""), FILTER(E2:E, E2:E <> ""), FILTER(F2:F, E2:E <> "");
  FILTER(A2:A, G2:G <> ""), FILTER(B2:B, G2:G <> ""), FILTER(G2:G, G2:G <> ""), FILTER(H2:H, G2:G <> "")
}),1,TRUE())

Sample output

Reference

• FILTER