blank rows

[greg malenky]

Excel 2000

I have found several ways to eliminate blank rows. But
this time I want to create a blank row between sections
of data.

example:

a b
john pittsburgh
bill pittsburgh
henry pittsburgh
(blank row)
tracy chicago
paul chicago
(blank row)
fred miami
tim miami
kevin miami

Each section has a maximum of 10 rows of data - however I
don't want more than 1 blank row between the data. Can I
use a countif with the row() functions?

Thank you
Greg Malenky

 

[Harlan Grove]

. . . I want to create a blank row between sections of data.

example:

a b
john pittsburgh
bill pittsburgh
henry pittsburgh
(blank row)
tracy chicago
paul chicago
(blank row)
fred miami
tim miami
kevin miami

...

Presumably this is desired final output, and the original list has no blank
rows. One way to do this involves using formula in columns C and D. For
simplicity I'll assume your original list is in A1:B8. Enter the following
formulas.

C1: =ROW()
D1: =B1

Select C11 and fill down into C28. Enter more formulas.

D9: =D1
D10: =INDEX(D$1$8,MATCH(0,COUNTIF(D$99,D$1$8),0)) [array formula]

Select D10 and fill down until formulas evaluate #N/A. Enter more formulas.

C9: =MAX(IF(D$1$8=D9,C$1:C$8))+0.5 [array formula]

Select C9 and fill down into the same rows that contain formulas in column D.
Now sort the entire range A1<whatever> on column C in ascending order. Once
sorted, delete the formulas in columns C and D.

This approach preserves the original order of your list.

 

[Harlan Grove]

"greg malenky" wrote...

...

Another way using fewer formulas. Assuming data without blank rows in A1:B8,
enter the following formulas.

C1:
=ROW()

C2:
=IF(C1<ROWS($A$1:$B$8),C1,#N/A)+IF(OR(INT(C1)-C1,OFFSET(B2,
SUMPRODUCT(2*(INT(C$1:C1)-C$1:C1)),0,1,1)<>OFFSET(B2,
SUMPRODUCT(2*(INT(C$1:C1)-C$1:C1))-1,0,1,1)),0.5,1)

Select C2 and fill down until the formulas evaluate #N/A. Sort the entire range
A1:C<whatever> on column C in ascending order. Once sorted, delete the formulas
in column C.