0

https://docs.google.com/spreadsheets/d/1hmgiKlSKmKPOE1ZVN70b6icgyU27IWoYrjE76OdnnEU/edit?usp=sharing

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.

2 Answers 2

0

drag down

I edited your file.

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.

1
  • Would you please include your answer as plain text.
    – Tedinoz
    Aug 2 at 3:24
0

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

snapshot


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

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.