copy 3 rows one column at a time

[Poida68]

Hi.
I have an (Excel 2003) array of 4 columns by 3 rows of data on a worksheet. I require a formula which will put all the data in a single column.

In other words i want to copy every 1st, 2nd and third row from every column

I had a look here. ... Excel formula: Copy value from every nth column | Exceljet

 

[hrayani]

Hello,

Book1
	A	B	C	D
1	DATA	DATA	DATA	DATA
2	NR	EJ	KJ	UA
3	EP	NT	YA	EC
4	NA	MG	LV	ND
5
6
7
8	NR
9	EP
10	NA
11	EJ
12	NT
13	MG
14	KJ
15	YA
16	LV
17	UA
18	EC
19	ND
Sheet1

Cell Formulas
Range	Formula
A8:A19	A8	=OFFSET($A$2,MOD(ROWS(A$1:A1)-1,3),INT((ROW(A1)-1)/3))

 

[earthworm]

Another approach

Book1
	A	B	C	D	E
1	x1	DATA	DATA	DATA	DATA
2		NR	EJ	KJ	UA
3		EP	NT	YA	EC
4		NA	MG	LV	ND
Sheet1

Follow below steps
1) ALT+D+P
2) Select Multiple Consolidated Ranges
3) Click Next
4) Select I will create the page fields
5) Select Range
6) Click Finish
7) Double click the value

There you get all in a single column

 

[Poida68]

Perfect Hrayani! Thanks so much.

 

[hrayani]

Perfect! Thanks so much.

you are welcome & thanks for the feedback