IF column is blank, show text, otherwise skip; multiple columns in on formula

[RobotsAteMyArms]

The title of this post is confusing as heck, so I apologize for that in advance! I know there's a way to do what I'm looking for (there's always a way), but I'm having a hard time with it this morning. I'm hoping you guys can help!

I'm looking to create a formula that will show, in the cell I enter the formula, the name of the deliverable(s) pending if the deliverable(s) do not have a received date. I can set it up to show me one at a time, but I want A2 to show me all four.

Here's what I mean: In the example below, I would want it to show "Deliverable 3" in A2, because that location is missing that deliverable. In A3, I want it to show "Deliverable 2; Deliverable 4", etc. I need it to skip over blanks and add ";"," " between text.

Where I'm getting hung up is having it check and return text, or nothing (for dated cells), on multiple. Doing them one at a time and then adding a =concatenate formula is a pain...

Right now I'm using, =IF(ISBLANK(C2),"Deliverable 1"," ") which doesn't work because I have to do it for each deliverable column separately and then because it's showing a space, instead of no value.

I have multiple deliverables that I want to report on for 700+ projects/locations, so to manually be sorting through and reading dates is just not an option.

What do you think? Is there a way to make it all one string?

 

[RobotsAteMyArms]

Actually, here's the actual example of what I'm using:


In Column A =IF(ISBLANK(U2),"BP","")


In Column B =IF(ISBLANK(W2),"CD","")


In Column C =IF(ISBLANK(AA2),"GC","")


In Column D =IF(ISBLANK(AD2),"SISD","")


In Column E =CONCATENATE(A2,";"," "B2,";"," "C2,";"," "D2)

That's what I'm doing now. Anyone have ideas on how to simplify it? Or make it less messy?

Because as of now, it takes a lot of steps and it also will show "; ; GC; " if that's the only deliverable pending. I can always run a couple 'find and replace's to clean those up, but that adds to the process.

 

[duds_sn]

Hey, I'm Brazilian so sorry if I say anything that doesn't make sense or is wrong.

Do you really need this as a formula or can we do this in VBA?