copy only cells which contain something

[hilcom]

Hi all,

I have a colom with in some cells an issue (see below, 0 - zero- means no content

How can I copy only those cells that contain something (A) to a new colom (B)?

Col A - Col B

aap - aap

jet - jet

0 - kokos

kokos - mies

mies - moeder

moeder - paard

paard - willem

willem - wim

0 - zus

wim - vader

0

0

0

zus

0

0

0

0

vader

0

0

0

 

[Luke M]

Do a normal copy of all the cells, and then delete the special ones:

Select all cells. Then Ctrl+G (goto dialogue), click on special, select either formula or constant, uncheck all the fields except for "number". hit the Delete key

 

[hilcom]

Thanks Luke M, but is does not fill up empty spaces;

I mean to produce an extra colom with 10 items instead of the original 23 and filled up from the top. It is almost the same as making a pivot and then copy the product. I know that procedure of course.

Bob, NL

 

[idunno]

Try using arrays. Make an array in another column say B or even another sheet, this array should just start with =if(a1=0,0,1) (considering your inputs as above are in row a starting from column 1) then in another array say column C, put C1 as =if(B1=0,0,b1)then in c2 put =if(b2=0,0,b2+sum($B$1:B1)) and copy this down as far as your list goes. In column D just put 1 in D1 and in d2 put =d1+1 and copy it down. In E put a match function to match the row where each existence of a none 0 cell is. IE: =match(d1,$c$1:$c$100,0) and run this all the way down, it will give you an array of the rows at which there are not zeros in column A, then make a final array with an indirect function finding A at those rows. IE =indirect("A"&E1) and copy that down and it will display your non blank cells consolidated at the top.

 

[Luke M]

Branching off of idunno's idea about arrays:

=IF(ROWS(B$2:B2)>COUNTA(A:A)-COUNT(A:A),"",INDEX(A:A,SMALL(IF($A$2:$A$100<>0,ROW($A$2:$A$100)),ROW($B1))))


Put this formula in B2 and copy down as far as would ever be needed. Adjust the range callout of A2:A100 if need by. Don't change the ROW($B1) part (it's there as a counter). Remember to use Ctrl+Shift+Enter to confirm an array formula.