I have a table where I need to split data within a row over two rows (and duplicating some of the columns in both rows).

An example row is:

| Class | Teacher | Day1 | Start1 | End1 | Day 2 | Start2 | End 2|
| 1A001 | Lauren  |   M  | 09:00  | 10:00|   W   | 10:00  | 11:00 |

I need this to be outputted (on a new tab within the same sheet ) as

| Class | Teacher | Day  | Start  | End 1 | 
| 1A001 | Lauren  |   M  | 09:00  | 10:00 |   
| 1A001 | Lauren  |   W  | 10:00  | 11:00 |

This would need to happen automatically (ideal) or when a script is run (as this is not a one-time conversion, with the data source being updated frequently, with the changes needed to be reflected in real-time in the output)

I have managed to achieve this through a Pivot Table in Excel with some script, but I now need to port my whole excel spreadsheet tool to Sheets and I am only just learning about Sheets.


After a long day of trying various ideas I’ve found on the internet that were overly complicated I finally stumbled upon a wonderfully simple formula of

=QUERY({Sheet1!B8:F;!B8:E;Sheet3!B8:E;Sheet4!B8:E},"select * where Col1 is not null",0)

Works a charm with no lag time since it’s not using importrange and it removed some weird errors I was getting from some sheets (not all) with Array_Filter errrors.

I did have to duplicate the classcode and teacher name fields so they were situated next to the second day data but it was easy to do based on the input data format I had.

If I find the original page I found this formula on, I’ll post the link to give credit where credit is due. But I had so many tabs open, I don’t think I saved the link.

  • I also had a look at your sheet and believe you will find it easier to split the timestamps by using e.g. for cells C4:C9 the next 2 formulas: For the left part =IFERROR(ArrayFormula(SPLIT(REGEXREPLACE(LEFT(C4:C7,11),"/","-"),"-")),"") and for the right one: =IFERROR(ArrayFormula(SPLIT(REGEXREPLACE(RIGHT(C4:C11,11),"/","-"),"-")),"") You can then format the results to your liking. Hope that helps a bit. :) Mar 1 '20 at 23:00
  • Your formula strongly reminds me of the ones I have posted on this answer as well as this one. Sorry I wasn't around earlier to help out. Good work :) Mar 2 '20 at 0:26
  • Have you seen the links provided? Did they help any? Just curious. Mar 5 '20 at 8:24
  • I am so sorry, I thought I deleted this question. I will look at it tomorrow at my desk.
    – Lauren
    Mar 6 '20 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.