# Creating a formula on output sheet to extract from range of cells using Min/Max (Need to skip 24 rows) and output w/out 24 rows between each row

How can I simply copy A4:D4 to A5:D5 but maintaining relative references 24 rows down on the Data! sheet.
In other words: I am wanting to extract the time of High per day and Low per day from Data!
Data sheet is too long to do this manually, as seen above.
I need a "formula" to grab the times so it displays as the three rows above do, for the entire Data! sheet.
Original Data set is 60k rows...
I believe =OFFSET can help but can't find the right way to write the whole formula in row 4 that can then just be copied and pasted into the underlying rows or better yet does it somehow automatically until the end of the dataset.

You can get what you want with a very complex, draggable formula, which I hope you can figure out for yourself. If you need any explanations, feel free to ask.

Sheet: Output, Cell: A3

`=query({arrayformula(vlookup(ARRAYFORMULA({query(query({Data!D3:D,arrayformula(to_date(int(Data!G3:G)))},"select Col2, Max(Col1) group by Col2 order by Col2 desc label Max(Col1) ''"),"select Col2 ")&"♥"&query(query({Data!D3:D,arrayformula(to_date(int(Data!G3:G)))},"select Col2, Max(Col1) group by Col2 order by Col2 desc label Max(Col1) ''"),"select Col1")}),query({arrayformula(Data!D3:D&"♥"&arrayformula(to_date(int(Data!G3:G)))),Data!D3:D,Data!E3:E,arrayformula(to_date(int(Data!G3:G))),arrayformula(index(SPLIT(Data!G3:G," "), 0, 2))},"select Col1, Col2,Col3,Col4,Col5 label Col1 'Unique Code', Col2 'High', Col3 'Low', Col4 'Date', Col5 'Time' format Col4 'yyyy-mm-dd', Col5 'hh:mm:ss'"),{4,5},0)),arrayformula(vlookup(ARRAYFORMULA({query(query({Data!E3:E,arrayformula(to_date(int(Data!G3:G)))},"select Col2, Min(Col1) group by Col2 order by Col2 desc label Min(Col1) ''"),"select Col2 ")&"♥"&query(query({Data!E3:E,arrayformula(to_date(int(Data!G3:G)))},"select Col2, Min(Col1) group by Col2 order by Col2 desc label Min(Col1) ''"),"select Col1")}),query({arrayformula(Data!E3:E&"♥"&arrayformula(to_date(int(Data!G3:G)))),Data!D3:D,Data!E3:E,arrayformula(to_date(int(Data!G3:G))),arrayformula(index(SPLIT(Data!G3:G," "), 0, 2))},"select Col1, Col2,Col3,Col4,Col5 label Col1 'Unique Code', Col2 'High', Col3 'Low', Col4 'Date', Col5 'Time' format Col4 'yyyy-mm-dd', Col5 'hh:mm:ss'"),{4,5},0))}, "select Col1,Col3,Col2,Col4 label Col1 'Day', Col3 'Date', Col2 'High', Col4 'Low' format Col1 'DDDD', Col3 'yyyy-mm-dd', Col2 'HH:MM:SS', Col4 'HH:MM:SS'")`

SAMPLE

Logic

• Unique codes are created for the combination of the Date and High value, and Date and Low value
• the data is analysed to identify the date of the HIGH value, and the data of the Low value.
• using the unique code of the respective Date and High/Low value, the time of the High/Low value can be identified using `VLOOKUP`.
• The output columns are a combination of the `VLOOKUP` for the High/Low values.

h/t: @player0 for answer in StackOverflow: Get column value when another column is max in google query when grouping