Copy non-blank cells

[cranmax]

Hello,

I'm new to this page so hello to all.
I've got two questions and what I want to achieve is Column B:

1. How could I... (I'll explain it the most graphic I can)

Column
A B
Rows: 1 3200
2 3200 world
3
4
5 world

Note: all the cells contain a formula, so the output of A1, A3, A5 is ""

2. How could I transform to Excel date?

Column
A B
Rows: 1 19820421 21/04/1982
2 19820422 22/04/1982
3 19820425 25/04/1982
4 19820426 26/04/1982

Thanks.

 

[cranmax]

I don't get your first request - sorry. It's far from clear.

Second request - this in B1 copied down:

=RIGHT(A1,2)&"/"&MID(A1,5,2)&"/"&LEFT(A1,4)

Yes, it works!

 

[cranmax]

It works too, thanks.

 

[FormR]

Here's the image similar to the other

Hi, images of hand written layouts are not a good way to describe your set-up.

Take a look here for some ways to post example data directly to the thread:

http://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[cranmax]

Hi, images of hand written layouts are not a good way to describe your set-up.

Take a look here for some ways to post example data directly to the thread:

http://www.mrexcel.com/forum/about-board/508133-attachments.html

Thanks for your apreciation but I'm posting from my mobile phone because I don't have Internet where I am. So what really matters is that you understand what I'm telling more than how I'm doing.

 

[FormR]

Hi, one of the problems with images is that they do not provide a permanent record for the forum - if you are not able to paste example data then a written explanation directly in the thread is a better way to describe your set-up.

Having said that, you could try this:

Excel 2013
	A	B	C	D	E
1	Header	Result		Helper	3
2	2	2
3	4	4
4		15
5	-8
6
7	15
Sheet1

Cell Formulas
Range	Formula
E1	=COUNTIF($A$2:$A$7,">0")
B2	{=IF(ROWS($B$2:B2)>$E$1,"",INDEX($A$2:$A$7,SMALL(IF(ISNUMBER($A$2:$A$7),IF($A$2:$A$7>0,ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS($B$2:B2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[cranmax]

Hi, one of the problems with images is that they do not provide a permanent record for the forum - if you are not able to paste example data then a written explanation directly in the thread is a better way to describe your set-up.

Having said that, you could try this:

Excel 2013
	A	B	C	D	E
1	Header	Result		Helper	3
2	2	2
3	4	4
4		15
5	-8
6
7	15
Sheet1

Cell Formulas
Range	Formula
E1	=COUNTIF($A$2:$A$7,">0")
B2	{=IF(ROWS($B$2:B2)>$E$1,"",INDEX($A$2:$A$7,SMALL(IF(ISNUMBER($A$2:$A$7),IF($A$2:$A$7>0,ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS($B$2:B2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks for for reply but I can't see completely the array formulas.

.

 

[FormR]

Thanks for for reply but I can't see completely the array formulas.

Hi, is this any better?

=IF(ROWS($B$2:B2)>$E$1,"",INDEX($A$2:$A$7,SMALL(IF(ISNUMBER($A$2:$A$7),IF($A$2:$A$7>0,ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS($B$2:B2))))

 

[cranmax]

Hi, is this any better?

=IF(ROWS($B$2:B2)>$E$1,"",INDEX($A$2:$A$7,SMALL(IF(ISNUMBER($A$2:$A$7),IF($A$2:$A$7>0,ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS($B$2:B2))))

Yes, I can see it and it works,now I have to adapt it to my spreadsheet, thanks for your effort.

Another question: following with your array formula, if the spreadsheet have 100 Rows and not 7, is possible to put it (100) on a cell (for example F1) and modify the formula from $A$7 to $A$(=F1)?

 

[FormR]

if the spreadsheet have 100 Rows and not 7

Hi, to make the ranges dynamic take a look at using tables and structured references or dynamic named ranges. Or alternatively adjust the 7 to a sensible maximum.