COPY UPSIDE DOWN DATA to one column with formula?

[abosiiqi]

[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]as1[/TD]
[TD]be1[/TD]
[TD]cu1[/TD]
[TD]di1[/TD]
[/TR]
[TR]
[TD]as2[/TD]
[TD]be2[/TD]
[TD]cu2[/TD]
[TD]di2[/TD]
[/TR]
[TR]
[TD]as3[/TD]
[TD]be3[/TD]
[TD]cu3[/TD]
[TD]di3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Result (That i need):[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]as1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]as2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]as3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]be1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]be2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]be3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cu1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cu2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cu3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]???[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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??
[TABLE="class: cms_table_cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]as1[/TD]
[TD]be1[/TD]
[TD]cu1[/TD]
[TD]di1[/TD]
[/TR]
[TR]
[TD]as2[/TD]
[TD]be2[/TD]
[TD]cu2[/TD]
[TD]di2[/TD]
[/TR]
[TR]
[TD]as3[/TD]
[TD]be3[/TD]
[TD]cu3[/TD]
[TD]di3[/TD]
[/TR]
[TR]
[TD]as4[/TD]
[TD]be4[/TD]
[TD]cu4[/TD]
[TD]di4[/TD]
[/TR]
[TR]
[TD]Result (That i need):[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]as1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]as2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]as3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]as4 (if i use that formula this column value is = be1) ??[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]be1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.