Cell reference equation??

  • Thread starter Thread starter MrSandman
  • Start date Start date
M

MrSandman

I've got a worksheet which has data all in one column but in every 5th
row. I'd like to copy the data over to another column so that the data
does not skip rows. In otherwords, the blanks are removed. How can i
do this? Is there an equation of some sort that i can have Excel copy
the data from every 5th cell? Please help!!!
 
Select the entire column, do Edit / Go To / Special / Blanks, then hit Edit /
Delete / Entire Row.
 
Thx for the response. Now lets say that the cells between them are no
blank. How would i extract this data? Will i have to run a macro
 
A bit fancy, although not necessarily inefficient...

Let A2:A28 house:

{"data";42;56;50;46;64;37;70;62;66;62;63;49;67;46;70;54;50;65;51;43;69;65;58
;40;77;90}

In B2 enter: 0 [required]

In B3 enter & copy down:

=IF((A3<>"")*INDEX(MOD(ROW($A$3:$A$28)-CELL("Row",$A$3)+0,5)=0,ROW()-ROW($A$
3)+1),LOOKUP(9.99999999999999E+307,$B$2:B2)+1,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,$B$3:$B$28)

In C3 enter & copy down (in fact, this copying should better be done with
code in VBA that refers to C1)

=IF(ROW()-ROW($C$3)+1<=$C$1,INDEX($A$3:$A$28,MATCH(ROW()-ROW($C$3)+1,$B$3:$B
$28,0)),"")

The result area will look like this:

{6;"list";42;37;63;54;69;90}
 
Hi
try the following formula in your second sheet (assumption column a
stores your values starting in row 1):
=OFFSET('sheet1'!$A$1,(ROW()-1)*5,0)
and copy this down
 
Back
Top