How can I use a formula in a header row to look down a specific column to search for blanks in that column, and if a blank is found: search the adjacent column in that same row where the blank is to see if there’s another blank next to it?
[This question is related to my other question, but I felt it was different enough that it deserved its own post for clarity. You can view my other post/question by clicking here.]
Badunit wrote:
I'm not sure there is a function that can determine if two adjacent cells are totally blank (those where ISBLANK would return TRUE). A formula that checks for "" (null string) will work for cells that are blank or have the formula result of "".
IF(COUNTIFS(B,"",C,"")>0,"Adjacent Blanks","No Adjacent Blanks")
I'm going to be honest, I don't know why I didn't try this when you posted it... I believe this is exactly what I'm looking for. I think I just didn't understand how it worked. Does it essentially act like an AND function, counting only if ALL conditions are met?
Badunit wrote:
Also, as with the COUNTIFS formula I provided, TEXTJOIN with the FALSE parameter gives the same result for "" or blank cells. If you need them to be absolutely blank, you may need to use a column of formulas to check each row individually with AND(ISBLANK(A),ISBLANK(B)).
I notice that TEXTJOIN with the TRUE parameter will skip over blank cells but will include cells with "".
This is an interesting observation. I was playing around with this and one way I found to be able to detect this and prevent this confusion in results would be to use a combination of =COUNTA and =COUNTIFS with regex to 'check' the data.
As a test, I input "" in cell B10. If I use your initial suggestion with the formula: "COUNTIFS(Table 1::A,"",Table 1::B,"")" I only get a result of 3 double blanks instead of 4 (since cell B10 is technically not blank), as you pointed out would happen. However, if I use "COUNTA(Table 1::A:B)" (which is the formula under "COUNTA") and "COUNTIFS(Table 1::A:B,REGEX("\d{1,3}((\,\d{3})+)*\.\d{2}",case-sensitive))" (which is the formula under "REGEX MATCH"), I can have another formula that checks those two numbers to make sure they are the same number. If so, there should be no cells with ""(null strings/fake blanks). It is essentially making sure the total number of cells that are not blank = all the cells that are formatted as they're supposed to be for my needs (as defined by my regex). (For anyone else, I suppose the regex could be changed to fit your needs or a different function might be used to compare to COUNTA).
Please correct me if I'm wrong but from here, by using this in addition to "COUNTIFS(Table 1::A,"",Table 1::B,"")" this should work flawlessly to detect two adjacent blanks while also making sure that null strings ("") do not throw off the data, correct?
=ISBLANK("") returns false.
=LEN("") returns 0.
A cell containing "" is not a blank cell!
Hence no bug in the TEXTJOIN function.
Paul.
See my post on bugs.
My test results:
SG
The way I understand your question you want the cell in the Header Column to indicate if you have any cells below that are blank and also have a blank cell next to them.
If so, then you can use TEXTJOIN like this:
The formula in B1, which checks cells in columns B and C in this example, is:
=IF(COUNTMATCHES(TEXTJOIN("~",0,B:C),"~~~")>0,"adjacent blanks","")
TEXTJOIN creates a ~ delimited string from values of the cells in the two columns. In
1~~3~~2~~4~~1/2~~6d~6d~~~~5~~8~0~0
If there are two adjacent cells that are "blank" then the pattern ~~~ will occur.
Use COUNTMATCHES on that and you have your answer.
SG
How to find if cell adjacent to a blank cell is also blank?
