2

I have a range of LibreOffice Calc cells A1:M218. Most rows have a "Y', for Yes, in a single column, with all other cells of the row blank. But some rows are completely empty. I want to identify the empty rows.

Is there a function that can be entered in column N that will highlight the empty rows by returning a specified character?

Also, please advise whether the solution would work in MS Excel, as well.

Improve this question
  • "can be entered in column N which will highlight the empty rows by returning a specified character" - could you please explain this? – Máté Juhász Aug 17 '16 at 12:44
  • Thanks for your speedy response. Any character to highlight that the row is empty i.e. none of the columns contain a "Y" – Lonscale Bear Aug 17 '16 at 12:55
  • Your range has 13 columns. It isn't clear whether all contain either Y or are empty (vs. some containing something else, or row labels, etc.). Also, is each row either completely blank or it contains Y in only a single cell, or can more than one column contain Y? Are the empty cells guaranteed to actually be empty, or might they contain some type of "empty" or "blank" placeholder? – fixer1234 Aug 17 '16 at 20:23
  • Those rows containing a Y will have a Y in one column only. All other cells in that row will be empty. Some rows will have no Y and all cells in the row will be empty.....it is these rows which I need identifying. Empty cells are guaranteed to be empty, with no placeholder function. – Lonscale Bear Aug 18 '16 at 14:28
  • @LonscaleBear, the system notifies people, other than the author, when there's a comment only if that person was the only other participant on a post. I just stumbled back across your comment. You can alert another participant by "addressing" the comment to them with @, like I did here. I posted an answer for you. I'll also clarify your question with the information in your comment so it will be easier for other people to answer. – fixer1234 Aug 19 '16 at 2:16
2

Note: This is a total rewrite to focus on the most applicable solutions.

The simplest solution is probably the following (shown for row 1):

=IF(COUNTIF(A1:M1,"Y"),"","Blank")

This relies on your description that the only exception to all blanks would be a "Y". This counts Y's. A count of zero is treated as false, which produces the word "Blank" as output (you can make that anything you want). Any count of 1 or more Y's is treated as true, and outputs null (an empty cell; it's entered as a pair of double-quotes with nothing between them).

Another way to do this is to look for the blank cells rather than the filled cells:

=IF(COUNTBLANK(A1:M1)=13,"Blank","")

There are 13 columns in the range A:M. This counts how many are blank. If all 13 are blank, the row is blank.

Note: In either case, you enter the formula once in column N and then just copy it or drag it to fill the results cells as needed. BTW, these will both work in Excel as well as Calc.

Improve this answer
  • I've used your fourth formula and it works like a charm. Many thanks for your time and expertise. – Lonscale Bear Aug 19 '16 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.