need to move data

[samber]

I need to move data from column A to column D eliminating all zeros.

Here is what I have now:

Column A
0
0
0
0
10000
20000
0
30000
0

Here is what I need:

Column D
10000
20000
30000

Thanks.

 

[Robert Mika]

Hi samber and welcome to the board.
If your data starts in A2(Array formula confirm CTRL+SHIFT+ENTER)
D2:
=IF(ROWS($A$2:$A2)<=COUNTIF($A$2:$A$999,">"&0),INDEX($A$2:$A$999,SMALL(IF($A$2:$A$999>0,ROW($A$2:$A$999)-ROW($B$2)+1),ROWS($A$2:$A2))),"")

copy down

 

[samber]

Thanks for the info.

This is what I am getting:

D2:

<TABLE style="WIDTH: 67pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=89><COLGROUP><COL style="WIDTH: 67pt" width=89><TBODY><TR style="HEIGHT: 18pt; mso-height-source: userset" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=24 width=89>0

</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2762337 class=xl71 height=17>#NUM!</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=17>#NUM!</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=17>#NUM!</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=17>#NUM!</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=17>#NUM!</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=17>#NUM!</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=17>#NUM!</TD></TR>

<TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2762329 class=xl71 height=17>#NUM!</TD></TR></TBODY></TABLE>

What did I do wrong?

 

[samber]

And what does this meana: Array formula confirm CTRL+SHIFT+ENTER?

 

[Robert Mika]

You confirm normal formula with ENTER only byt with array
Hold SHIFT and CONTROL together and the press ENTER:

Excel Workbook
	A	B	C	D	E
1	*	*	*	*	*
2	0	*	*	10000	*
3	0	*	*	20000	*
4	0	*	*	30000	*
5	0	*	*	*	*
6	10000	*	*	*	*
7	20000	*	*	*	*
8	0	*	*	*	*
9	30000	*	*	*	*
10	0	*	*	*	*
11	*	*	*	*	*
12	*	*	*	*	*
Sheet1

 

[samber]

Thanks Robert.