COPY UPSIDE DOWN DATA to one column with formula?

[abosiiqi]

A	B	C	D
as1	be1	cu1	di1
as2	be2	cu2	di2
as3	be3	cu3	di3
Result (That i need):
as1
as2
as3
be1
be2
be3
cu1
cu2
cu3
???

<tbody>
</tbody>

Hello all, is that possible to make a formula for other data on "???", my data need to compile as one column? and the pattern like upside down..

Thanks,


Ammar

 

[chicagocomputerclasses]

Assuming you start at the cell A6

=INDEX($A$1:$D$3,MOD(ROWS(A$6:A6)+2,3)+1,INT((ROW()-ROW($A$6))/3)+1)

 

[abosiiqi]

Assuming you start at the cell A6

=INDEX($A$1:$D$3,MOD(ROWS(A$6:A6)+2,3)+1,INT((ROW()-ROW($A$6))/3)+1)

Thank you very much , but what if my case like this?
if i use that formula with this condition, the result was not as4 but be1 what should i change??

A	B	C	D
as1	be1	cu1	di1
as2	be2	cu2	di2
as3	be3	cu3	di3
as4	be4	cu4	di4
Result (That i need):
as1
as2
as3
as4 (if i use that formula this column value is = be1) ??
be1

<tbody>
</tbody>

 

[chicagocomputerclasses]

You will need to modify the formula a bit

=INDEX($A$1:$D$4,MOD(ROWS(A$6:A6)+3,4)+1,INT((ROW()-ROW($A$6))/4)+1)

 

[Mike Szczesny]

Hi abosiiqi,

Here is a crazy complicated formula. However, this is a robust formula dependent on your data range. So there would be no need to tweak the formula each time your data range changes. One formula shows the numbers listed down by column. The other formula shows the numbers listed down row. Use C-S-E, then copy down

Excel 2012
	A	B	C	D
1	a	d	g	j
2	b	e	h	k
3	c	f	i	l
4	m	x	n	o
5
6	a		a
7	d		b
8	g		c
9	j		m
10	b		d
11	e		e
12	h		f
13	k		x
14	c		g
15	f		h
16	i		i
17	l		n
18	m		j
19	x		k
20	n		l
21	o		o
Sheet1

Cell Formulas
Range	Formula
A6	{=IFERROR(INDEX($A$1:$D$4,INT(SMALL(IF($A$1:$D$4<>"",(ROW($A$1:$D$4)-ROW($A$1)+1)*10^9+COLUMN($A$1:$D$4)-COLUMN($A$1)+1),ROWS($A$6:A6))/10^9),MOD(SMALL(IF($A$1:$D$4<>"",(ROW($A$1:$D$4)-ROW($A$1)+1)*10^9+COLUMN($A$1:$D$4)-COLUMN($A$1)+1),ROWS($A$6:A6)),10^9)),"")}
C6	{=IFERROR(INDEX($A$1:$D$4,MOD(SMALL(IF($A$1:$D$4<>"",(ROW($A$1:$D$4)-ROW($A$1)+1)*10^9+COLUMN($A$1:$D$4)-COLUMN($A$1)+1),ROWS($C$6:C6)),10^9),INT(SMALL(IF($A$1:$D$4<>"",(ROW($A$1:$D$4)-ROW($A$1)+1)*10^9+COLUMN($A$1:$D$4)-COLUMN($A$1)+1),ROWS($C$6:C6))/10^9)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.