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

What end result do you want?

If there are values in both A1 & C1, etc... do you want a single value placed in cell M10 that references that fact? In otherwords... Do you want M10 to say...
"A1 & C1", or "234A & 4T41", or "2 cells not blank", or what?

And if that's the case, are you addressing each row separately? In otherwords... Will you want to display row 2's results in M11 ?

Or are you just trying to post the table, without the blanks? In otherwords... A1:D7 would become M10:P16, except it is displayed like this...
234A N333 4T41 7657
11C2 321 3T27
L954 989
56D1 297-1
87E3 P199
D992
Z222 3391

Just trying to help...

MSY12,

Thanks for your reply. I have a spreadsheet with approximately 12,000 rows of data. It's being used as a database. If certain cells of data of the same row have data and are not blank, I want to copy the entire row of data to another spreadsheet. I am having difficulty testing the cells of data which maybe numeric or alpha numeric. The possible combinations of cells are 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.

Thanks!

Shg,

I'm confused. I re-read your prior reply and realize I may not have replied correctly to you. I am interested in the following only:

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.

I need to be able to test for these 4 possible combinations. If any one of these combinations is not a blank then I need to copy this entire row of data. My spreadsheet has over 13,000 rows of data with 17 columns/cells per row.

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!

Shg,

I'm sorry, I didn't see your reply and I've been having some problems logging onto the website. If there is a match of data using your formula, I wish to copy the row of data to another sheet in the same workbook. Attached is sample data. The columns that we are testing if blank or not at L, M, O, & P so your formula would look like this =COUNTA(L1:M1)*COUNTA(O1:P1).

Thanks!

Shg,

I am not familiar with the code that you posted. I've never used anything like this. Please let me know how and where do I use this. Sorry for not being Excel savy.

Thanks!

Shg,

I received the following error after I pressed "RUN". I am not sure what this means or what to do.
Thanks!


---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Expected End Sub
---------------------------
OK Help
---------------------------

Shg,

I did copy "all" of the code. I did this a couple of times and I continue to receive the same error.

Thanks

Shg,

I was preparing an "abbreviated" workbook to send to you. I reduced the number of rows from over 12,000 to approximately 200 rows which represented the data that we need to test for. Once I reduced the number of rows, I ran the macro you provided and it worked. When I ran the macro on the original spreadsheet with the over 12,000 rows of data I I received the following error:

Microsoft Visual Basic
Run-time error '1004':
Application-defined or object-defined error

The choices offered are: "End" "Debug" "Help"

When I select debug, the following is highlighted in yellow with an arrow to the left.

With r.Offset(, r.Columns.Count).Resize(, 1)

Any thoughts? I can't send the original spreadsheet.

Thanks!

Shg,

It works!!! Thanks for all your help!! It took about 18 minutes for the macro to run. Is there an alternative way to do this that maybe faster?

Thanks Again!!!!