Flag multiple rows of a record if one row is missing data in Excel

Question

I have been struggling to find a solution to the problem best explained by the image below.

What I am trying to do is flag a series of cells (which make up a single record over a number of rows) if one of the record’s rows contains a blank.

As you can see from the image, cell C7 is missing the year so all records of BBB will need to be flagged as they will be potentially invalid in my data. The records will likely be manually cleaned or removed at a later stage.

I am easily able to create a flag for the one row where the year is missing (with ISBLANK) but I need a the other flag cells of the same ID to be filled as well.

If anyone has any thoughts as to a solution to this I would be very grateful. Thanks!

Answer 1

I think this will work in 2007 / 2010:

If it returns an error or incorrect result, try confirming it as an array formula by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

=IF(MOD(SUMPRODUCT(N(LEN(IF($A$2:$A$10=A2,IF($A$2:$D$10="","",$A$2:$D$10),""))>0)),4)=0,"",1)

algorithm

• Return an array of just the rows the match with ID

• Do some manipulations to return "" instead of 0 for the unmatched rows

• Count the number of resultant strings with length > 0

• Check that the number of strings with length > 0 is a factor of 4.

  • If not, output a flag.