Google-sheets – For range X, when empty grab column A and list them in a single cell, coma separated

formulasgoogle sheets

I'm compiling stats for our Boom Beach team, which means I have to log attacks by each players for each operations. An operation is launched every day and all players should perform their attack in that operation.

I'd like to make it easier to list players who did not perform their attack, for each operation.

What I'd like is to have a coma-separated list of players whose attack is blank.

Here's a sample sheet to make it clearer.

What I'd like to automate is the red part, so I can simply copy-paste the value of that cell into our slack chatroom and kick some butts.

Of course, I could CONCAT a bunch of IFS, but the formula would be hell to maintain since I'm constantly adding new players and removing players.

In the end, if I could have a formula that says "For range B2:B6, when empty grab what's in column A and list them in a single cell, comma separated"

Best Answer

Use JOIN subject to FILTER by ISBLANK. For the example spreadsheet below, the command

=JOIN(", ", FILTER(A1:A6, ISBLANK(B1:B6)))

returns "Robert, Jay, Tim"

      A          B
1    Jon      attacked   
2    Robert     
3    Grace    attacked  
4    Jay        
5    Tim        
6    Shog     attacked
Related Topic