Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Row Compression
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 |
#2
|
|||
|
|||
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 "W. Watson" wrote in message ink.net... 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 |
#3
|
|||
|
|||
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" wrote in message ink.net... 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 |
#4
|
|||
|
|||
Duke Carey wrote:
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" wrote in message ink.net... 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 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 |
#5
|
|||
|
|||
"W. Watson" wrote
... 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: 1 x 2 3 z 4 m 5 p 6 c -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Troubel with compression of a very large Excel file | Excel Discussion (Misc queries) |