Reorder the 3 column data into 2 columns of 24 rows

test.xlsx

I want to transform this data into two columns as Day 1 and Day 2 , starting with the day 1 first value and end day 1 for 24 hours later (2pm-2pm next day as day 1) and next 24 values as day 2. The start time for Day 1 keeps changing for different scenarios. I want a dynamic formula to Setup day 1 and day 2. I have 200+ such sheets. So a quick and easy process is much appreciated.

Column A-D to Column J-L

Try these:

In J13:

Formula:

Please Login or Register  to view this content.

In K13:

Formula:

Please Login or Register  to view this content.

In L13:

Formula:

Please Login or Register  to view this content.

Copy down.

I have done it in columns N, O & P, so as to compare with your hard coded data.

Those formulae rely on the data keeping this pattern of number of rows between the days, for example, the first day times start in A13 and the second in A51, 38 rows apart, thus the MATCH +38 or +0 in the formula.

I hope this helps you.

Good luck!

Thankyou very much ! This works perfectly fine.

You're welcome. Glad to help.
Thank you for the feedback and for the reputation added.
Have a blessed day!

Hi,

I just realized that these formulae are referencing to rows 13 to 74. But the data is only from 13-36 rows. I tried to copy and paste the formula for new columns with new values and it is not working for values past first 6 rows.

* Also, I have a multiplicative factors for each column (B,C,D). Each column has it's own factor. Can you help me multiply the values and then order them in new format of Day 1 and day 2. *