Row Compression

[W. Watson]

Is it possible to remove rows that are blank in some column?

Suppose I have

a 4
b 6
8
d 2
e 9

And want to remove rows with a blank in column 1 and collapse the whole array to
get:

a 4
b 6
d 2
e 9

--
Wayne T. Watson (Watson Adventures, Prop., Nevada City, CA)
(121.015 Deg. W, 39.262 Deg. N) GMT-8 hr std. time)
Obz Site: 39° 15' 7" N, 121° 2' 32" W, 2700 feet

"Academic disputes are vicious because so little
is at stake." -- Anonymous

Web Page: <home.earthlink.net/~mtnviews>

 

[Jim Cone]

Wayne,

Select the entire data set.
Sort by the column with the blanks.
The blank cells will sort to the bottom allowing you
to delete the rows as a group.

Regards,
Jim Cone
San Francisco, USA

 

[Duke Carey]

If the order of your rows has to be maintained, you can insert a new column
A, fill it with a series of numbers, 1 thru n, then sort on the column with
the blanks, delete the 'bad' rows, re-sort by the column with the series in
it, and finally delete that column.

Otherwise, use Jim's suggestion

 

[W. Watson]

If the order of your rows has to be maintained, you can insert a new column
A, fill it with a series of numbers, 1 thru n, then sort on the column with
the blanks, delete the 'bad' rows, re-sort by the column with the series in
it, and finally delete that column.

Otherwise, use Jim's suggestion

Good suggestions. Let me ask the almost opposite question. Is it possible to
expand an array based on the absence of some data. In particular, suppose I have:

1 x
3 z
4 m
5 p
6 c

But need to have a record for every sequential digit from 1 to 6, so that I have:

1 x
2
3 z
4 m
5 p
6 c

A real case is that I have a report with records for dates events occurred on
but not the dates that the events did not occur on. It's quite possible I want
records for the entire set of dates from start to finish, 61 consecutive dates,
and not just the 40 that were recorded.

--
Wayne T. Watson (Watson Adventures, Prop., Nevada City, CA)
(121.015 Deg. W, 39.262 Deg. N) GMT-8 hr std. time)
Obz Site: 39° 15' 7" N, 121° 2' 32" W, 2700 feet

"Academic disputes are vicious because so little
is at stake." -- Anonymous

Web Page: <home.earthlink.net/~mtnviews>

 

[Max]

... Is it possible to expand an array based on the
absence of some data. In particular, suppose I have:

1 x
3 z
4 m
5 p
6 c

But need to have a record for every sequential digit from 1 to 6, so that I have:

1 x
2
3 z
4 m
5 p
6 c

Try this ..

Assume data below is in A1:B5

1 x
3 z
4 m
5 p
6 c

Put in D1:
=IF(ROWS($A$1:A1)<=MIN(A:A),MIN(A:A),IF(ROWS($A$1:A1)>MAX(A:A),"",ROWS($A$1:
A1)))

Put in E1:
=IF(ISNA(MATCH(D1,A:A,0)),"",INDEX(B:B,MATCH(D1,A:A,0)))

Select D1:E1, fill down until blanks appear in cols D & E,
signalling completion of fill

You'll get in D1:E6: