Moving 1 column into several columns based on an empty row-CodePudding

I'm trying to tidy up a spreadsheet from Google Sheets, essentially I only have two columns (date and value) but 3 different sets of data (separated by an empty row). I want to be able to grab everything after (in between) each empty row and paste it to the corresponding column. For example:

1 26
2 12
3 50
(empty row)
1 23
2 21
3 25
(empty row)
1 23
2 29
3 78

And I'd want it to be like this:

1 26 1 23 1 23
2 12 2 21 2 29
3 50 3 25 3 78

I've found some functions for detecting empty rows but wasn't sure where to go from there, sorry I'm a novice at Excel/Google Sheets. Any help would be greatly appreciated! TIA!

Here is a link to my sample spreadsheet (tab 1 is the sample data, tab 2 is what I'm trying to achieve)

CodePudding user response：

Formula:

=arrayformula(split(transpose(split(join(",", flatten(
 query(transpose(split(flatten(split(regexreplace(join("×", A1:A11&","&B1:B11), "×,×", "★"), "★")), "×")), 
 "select Col1, Col2, Col3, '×' label '×' ''", 0))), "×")), ","))

Formula:

Output: