Testing for Blank Cells

Shg,
I am not an Excel expert by any means, but here's what I've tried.

=IF(OR(ISBLANK(A1),ISBLANK(C1)),"",M10),(OR(ISBLANK(A1),ISBLANK(D1)),"",M10),(OR(ISBLANK(B1),ISBLANK(C1)),"",M10),(OR(ISBLANK(B1),ISBLANK(D1)),"",M10),

Basically there are 4 combinations that I'm trying to test. I'm using M10 to represent the record that I wish to copy to another spreadsheet. Below is what the spreadsheet looks like; it got disoriented in my original posting.


--------A -------B ------C ------D
R1 ---234A--- N333---4T41 ---7657
R2 ---11C2 ---321 ----3T27
R3 -----------L954 -------------989
R4 -56D1-----297-1-----------
R5 --------------------87E3 ----P199
R6- D992-------------------------
R7 -Z222------------------------3391

Thanks!

I'm don't think this works. The possible combinations to copy the complete record as as follows: A1 and C1 combined must not be blank; or A1 and D1 combined must not be blank; or B1 and C1 combined must not be blank; or B1 and D1 combined must not be blank. If one cell of any of the four combinations is blank, then I do not want to copy the record. Thanks!

Shg,

I need to do both. Thanks

Shg,

Please see my first posting. The sample data is correct as posted. When I initially posted this, it was disoriented. Row 1, copy; row 2, copy; row 3, copy; row 4, no copy; row 5, no copy; row 6, no copy, row 7, copy.

From my perspective, there are 4 possible "2 cell" combinations that need to be tested if blank or not. If one cell of the 2 cell combination is blank, this would result in a no copy. Thanks for your help!

Okay...

It seems to me, you will have to actually write a macro to paste each individual row into a new workbook or worksheet. I'll try to mess with how to put that all together over some time...but try to get it yourself as well...

So far...I think this is what you'll need to get started...

=IF(COUNTA(A1),IF(COUNTA(C1),"2",IF(COUNTA(D1),"2","1")),IF(COUNTA(B1),IF(COUNTA(C1),"2",IF(COUNTA(D1),"2","0")),"0"))

Paste that at the end of 1st row (E1, for example), and then drag it down. That will count whether there are values in A1 & C1, A1 & D1, B1 & C1, or B1 & D1...in that order. If any are true, they return "2". Once you have the "2", you will be able to write a macro to extract rows with a "2" in column E:E.

I hope that leads you in the right direction...

Shg,

Thanks for your help. I have a couple additional questions. Sorry for not being Excel savy.

Your formula for the testing the data does work. Our spreadsheet has 18 columns of data and over 12,000 rows of data. I wish to use your formula to test each row of data and when there is a match copy the entire row to another spreadsheet. How can I set up you formula to read each row without having to define the last row of data? Also, is there a way to copy each row of data as a whole row rather than on a cell by cell basis?

Thanks

Shg,

Thanks for the formula. I'm sorry that I didn't understand the first time around. I do have additional questions. We have a spreadsheet that being used to collect data. It presently has over 12,000 rows, 18 columns wide. I wish to use your formula to test each row and it there is a match, copy the row to another spreadsheet. How can I set up the formula to to read the 12,000 rows without naming the final row so that if more rows are added to the formula does not have to be modified? Also, is there an faster way to copy the entire row when there is a match rather than each cell of the row?

Thanks!