Cable Forum - excell deleting rows

Cable Forum (https://www.cableforum.uk/board/index.php)

- General IT Discussion (https://www.cableforum.uk/board/forumdisplay.php?f=19)

- - excell deleting rows (https://www.cableforum.uk/board/showthread.php?t=33620193)

ikthius

29-08-2007 15:21

excell deleting rows

hey everyone,

is there a way to delete rows if all criteria are met?

what I mean is this.....

I can generate a report and send it to excel,
if I have 5 columns say and I am missing info in one column, I want to keep the row, if the row is full, I dont need it, so delete it

Code:

1

1, 2, 3

1, 2, 3, 4, 5 delete this as it has all the info

1, 2, 3, 4, 5 delete this as it has all the info

so that I am left with only the rows that dont have all columns filled.

I must explain at this point, that the information is stored in a database, the report is really for me to fill in the blanks, if you know what I mean.
so I don't want to trawl through hundreds of sheets of paper, to update only a handful of rows.

ik

gazzae

29-08-2007 15:31

Re: excell deleting rows

Add in a new column with the formula...

=OR(ISBLANK(A2),ISBLANK(B2),ISBLANK(C2),ISBLANK(D2 ))

and then filter out all the Falses.

Aragorn

29-08-2007 15:32

Re: excell deleting rows

You could add an extra calculated column, something like

if (A1<>" " and B2 <> " " and ..., "All fields present","not all there")

Sort by the new column and manually delete all the one saying 'All fields present'. (Sorry, I can't remember the exact if/and syntax!)

Alternatively use a database query to only select the interesting records, eg Select a, b ,c from table where a = " " or b = " " or c = " ";

ikthius

29-08-2007 15:35

Re: excell deleting rows

Quote:

Originally Posted by Aragorn (Post 34385880)

You could add an extra calculated column, something like

if (A1<>" " and B2 <> " " and ..., "All fields present","not all there")

Sort by the new column and manually delete all the one saying 'All fields present'. (Sorry, I can't remember the exact if/and syntax!)

Alternatively use a database query to only select the interesting records, eg Select a, b ,c from table where a = " " or b = " " or c = " ";

sorry, it has predefined reports, we cannot change.

Gazzae, thats worked nicely enough, thanks

ik

All times are GMT. The time now is 10:55.