Transfer every nth cell to another column

[Svenicide]

Hi everyone,

I'm trying to copy hourly meteorological data from every 24th cell in a column to a new column, then repeat this going down eg. for 12:00:00 copy data in cells B3, B27,..., B8763 to E3,E4,...,E364, and then for 01:00:00 copy data in cells B4, B28,...,B8764 to F3,F4,...,F364 and so on.

I have tried using the OFFSET function (as shown on image) to no avail. I'm guessing this is probably a VLOOKUP formula, but couldn't get it to work.

Would prefer not to use VBA code (I have several spreadsheets and there is a lot more data than this).

EDIT: Column A contains the Julian date and time (probably why VLOOKUP is not working?)

Part of the spreadsheet is at this link:

http://imagizer.imageshack.us/a/img538/7222/vcHpbp.jpg


Kind regards

 

[Blade Hunter]

I can't test because your data is an image.

Having said that though, I believe you can do this yourself with these hints:

Take your working formula.

Use the following functions with it:

row
Indirect

So, how it works is where you have your cell references, sub in (Row(A4)-Row(A$3))*24 to get the row that it SHOULD reference for the second set. Each time you drag this down it will resolve to 24, 48 etc etc etc.

Once done you just need that in an Indirect function to resolve the string to an address.

The formula would be as simple as (in E4) =Indirect("B" & (Row(A4)-Row(A$3))*24)

Hope that helps

Dan

 

[Svenicide]

Hi Dan,

Thank you very much for your help - I've tried it but I'm not entirely sure how the code should read.

Below should be the HTML for part of the spreadsheet if you could take a look and see if the formula you're proposing would work?

Excel 2007
	A	B	C
2		Obs Temp (C)	12:00:00 AM
3	12:00:00 AM	17	17
4	1:00:00 AM	17	18
5	2:00:00 AM	17	17
6	3:00:00 AM	17	18
7	4:00:00 AM	16	19
8	5:00:00 AM	18	20
9	6:00:00 AM	19	20
10	7:00:00 AM	20	21
11	8:00:00 AM	20	20
12	9:00:00 AM	21	20
13	10:00:00 AM	22	20
14	11:00:00 AM	23	21
15	12:00:00 PM	23	22
16	1:00:00 PM	23	26
17	2:00:00 PM	23	24
18	3:00:00 PM	23	26
19	4:00:00 PM	23	24
20	5:00:00 PM	23	27
21	6:00:00 PM	22	20
22	7:00:00 PM	20	19
23	8:00:00 PM	19	20
24	9:00:00 PM	18	22
25	10:00:00 PM	17	23
26	11:00:00 PM	18	23
27	12:00:00 AM	17	21
MR EXCEL

Cell Formulas
Range	Formula
C4	=INDIRECT("B"&(ROW(A4)-ROW($A$3))*24)
C5	=INDIRECT("B"&(ROW(A5)-ROW($A$3))*24)
C6	=INDIRECT("B"&(ROW(A6)-ROW($A$3))*24)
C7	=INDIRECT("B"&(ROW(A7)-ROW($A$3))*24)
C8	=INDIRECT("B"&(ROW(A8)-ROW($A$3))*24)
C9	=INDIRECT("B"&(ROW(A9)-ROW($A$3))*24)
C10	=INDIRECT("B"&(ROW(A10)-ROW($A$3))*24)
C11	=INDIRECT("B"&(ROW(A11)-ROW($A$3))*24)
C12	=INDIRECT("B"&(ROW(A12)-ROW($A$3))*24)
C13	=INDIRECT("B"&(ROW(A13)-ROW($A$3))*24)
C14	=INDIRECT("B"&(ROW(A14)-ROW($A$3))*24)
C15	=INDIRECT("B"&(ROW(A15)-ROW($A$3))*24)
C16	=INDIRECT("B"&(ROW(A16)-ROW($A$3))*24)
C17	=INDIRECT("B"&(ROW(A17)-ROW($A$3))*24)
C18	=INDIRECT("B"&(ROW(A18)-ROW($A$3))*24)
C19	=INDIRECT("B"&(ROW(A19)-ROW($A$3))*24)
C20	=INDIRECT("B"&(ROW(A20)-ROW($A$3))*24)
C21	=INDIRECT("B"&(ROW(A21)-ROW($A$3))*24)
C22	=INDIRECT("B"&(ROW(A22)-ROW($A$3))*24)
C23	=INDIRECT("B"&(ROW(A23)-ROW($A$3))*24)
C24	=INDIRECT("B"&(ROW(A24)-ROW($A$3))*24)
C25	=INDIRECT("B"&(ROW(A25)-ROW($A$3))*24)
C26	=INDIRECT("B"&(ROW(A26)-ROW($A$3))*24)
C27	=INDIRECT("B"&(ROW(A27)-ROW($A$3))*24)
C2:Z2	{=TRANSPOSE(A3:A26)}
C3:Z3	{=TRANSPOSE(B3:B26)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Kind regards,

Joel