All pastes #1969602 Raw Edit

Untitled

public text v1 · immutable
#1969602 ·published 2010-10-22 04:57 UTC
rendered paste body
Insert Blank line formula ( assume data is in column a drag to the right with neighboring columns; presents zeros if there are blank entries in the cells of the column your trying to space)
=IF(ISERROR(IF(ROW()=1,A$1,INDIRECT(IF(AND((ROW()-1=-1+ROW()+(1+(-1^(ROW())))),NOT(((ROW()+1)/2)=((-1+ROW()+(1+(-1^(ROW())))))/2))=TRUE,ADDRESS(IF(MOD(ROW(),2)>0,(ROW()+1)/2,ROW()/2),COLUMN(A1)),""))))=TRUE,"",IF(ROW()=1,A$1,INDIRECT(IF(AND((ROW()-1=-1+ROW()+(1+(-1^(ROW())))),NOT(((ROW()+1)/2)=((-1+ROW()+(1+(-1^(ROW())))))/2))=TRUE,ADDRESS(IF(MOD(ROW(),2)>0,(ROW()+1)/2,ROW()/2),COLUMN(A1)),""))))

(no zero output version; treats blank lines as a entry so it will have three lines between two non-blank entries
=IF(INDIRECT(ADDRESS(IF(MOD(ROW(),2)>0,(ROW()+1)/2,ROW()/2),COLUMN(A1)))=0,"",IF(ISERROR(IF(ROW()=1,A$1,INDIRECT(IF(AND((ROW()-1=-1+ROW()+(1+(-1^(ROW())))),NOT(((ROW()+1)/2)=((-1+ROW()+(1+(-1^(ROW())))))/2))=TRUE,ADDRESS(IF(MOD(ROW(),2)>0,(ROW()+1)/2,ROW()/2),COLUMN(A1)),""))))=TRUE,"",IF(ROW()=1,A$1,INDIRECT(IF(AND((ROW()-1=-1+ROW()+(1+(-1^(ROW())))),NOT(((ROW()+1)/2)=((-1+ROW()+(1+(-1^(ROW())))))/2))=TRUE,ADDRESS(IF(MOD(ROW(),2)>0,(ROW()+1)/2,ROW()/2),COLUMN(A1)),"")))))

or

double space a range of values
number 1-end in the right of the data set and then put the same 1-end below the first set. sort the data range with the repeated values