Mar 10, 2022
Rearranging cells
So that I have the value from A1 to A24 in one column and then A25 to A48 in the next column and so on. This would line up the data with one 24-hour period in each column.
I'm sure there must be an easy and/or clever way to do this without having to chop up the data manually.
https://docs.google.com/spreadsheets/d/13fZYpWeijaqAQrcQOfzQQfoHE9RgxslnnPv336DJAJY/edit
Details
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Last edited Mar 11, 2022
Mar 11, 2022
Hi Anders,
I'm sure Erik's solution will soon be tweaked to give you the answer you need.
And I'll also throw in my effort. See tab GK.Help.
The formula in G1 basically does a lookup into column A, and uses the "row values" of 1 to 24, plus 24 for every column it moves over, as the lookup value.
And the lookup range is a virtual array made up of the row number, and the value, of every cell in column A.
=ArrayFormula( VLOOKUP(ROW(G1:G24) + 24*(COLUMN(G1:Z1)-COLUMN($G$1)), {ROW(A1:A),A1:A},2,1)) I've limited the output to go to column Z.
You could change that, by using a count of the number of rows in column A, divided by 24, and that would tell you how many columns you need to go across. Just convert it to a column letter, and include that as part of the formula.
Let me know if that helps.
Cheers,
Gill
Original Poster Anders Elkjaer Johansen marked this as an answer
All Replies (6)
Mar 11, 2022
I think you have the right idea and your formula is well beyond my current capabilities, but I noticed that the numbers don't match. In the current layout, the value in A1 should be equal to the value in B2 and A2=B3 etc. But they don't match. Do you have any idea why that is?
Last edited Mar 11, 2022
Mar 11, 2022
Hello Anders,
I noticed that you said you have a row for each year, but in the sample you shared, you didn't actually include any years?
I noticed that you said you have a row for each year, but in the sample you shared, you didn't actually include any years?
If you include the years, and any information in a "header row" (whether it's in english or not), it might help folks and make the various formulas needed to rearrange your data a little simpler to understand.
Cheers,
Matt
Matt
Hi Anders,
I'm sure Erik's solution will soon be tweaked to give you the answer you need.
And I'll also throw in my effort. See tab GK.Help.
The formula in G1 basically does a lookup into column A, and uses the "row values" of 1 to 24, plus 24 for every column it moves over, as the lookup value.
And the lookup range is a virtual array made up of the row number, and the value, of every cell in column A.
=ArrayFormula( VLOOKUP(ROW(G1:G24) + 24*(COLUMN(G1:Z1)-COLUMN($G$1)), {ROW(A1:A),A1:A},2,1)) I've limited the output to go to column Z.
You could change that, by using a count of the number of rows in column A, divided by 24, and that would tell you how many columns you need to go across. Just convert it to a column letter, and include that as part of the formula.
Let me know if that helps.
Cheers,
Gill
Original Poster Anders Elkjaer Johansen marked this as an answer
Mar 11, 2022
Thanks Matt,
I tried to cut out as much as possible to remove personal information.
Last edited Mar 11, 2022
Mar 11, 2022
Last edited Mar 11, 2022
Mar 11, 2022
I've placed this formula in cell B2 on the tab called MK.simple and copy/dragged it down 24 rows and over to the right 3 days and it seems to be working.
=INDEX($A:$A,(COLUMN()-2)*24+ROW())Hope this helps!
Matt
Last edited Mar 11, 2022