Concatenate and add a condition for blank cells

[Boubiche]

I have a question and I hope I am able to formulate it right. I've tried but cant seem to get it to work.

I have a bunch of activities (Column A) with different options of classes

I want cells in D:D to concatenate the different classes, (in column B and C), however, for some of the activities, there is no option of class.. so these B & C are empty. In column D, for that activity, the return is blank.

It is possible to either:

• To add a text to blank D:D cells?
• concatenate the name of the class (Column A) with the rest of the formula only if column B and C are empty.. because I don’t need the rest of D:D to contain that information.

Basically, how do I fill D:D with some info about the activity since there is no class to put in there!

Activity	Eng Class	FR Class	Final
Activity A	Class A		Class A
Activity B	Class B		Class B
Activity C		Class D	Class D
Activity D	Class C		Class C
Activity E
Activity F
Activity G	Class B		Class B
Activity H
Activity I
Activity J		Class B	Class B

Hope this makes sense!!

I thank you all amazing people!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Not sure if it is possible that in a row both column B & C can have data like row 12 below?
If so perhaps you want the column D formula.
If not then column E might be what you want?

If you have lots of columns instead of just B & C then something a bit different might be needed but that could depend on your response to the first paragraph above.

22 03 28.xlsm
	A	B	C	D	E
1	Activity	Eng Class	FR Class	Final	Final
2	Activity A	Class A		Class A	Class A
3	Activity B	Class B		Class B	Class B
4	Activity C		Class D	Class D	Class D
5	Activity D	Class C		Class C	Class C
6	Activity E			Activity E	Activity E
7	Activity F			Activity F	Activity F
8	Activity G	Class B		Class B	Class B
9	Activity H			Activity H	Activity H
10	Activity I			Activity I	Activity I
11	Activity J		Class B	Class B	Class B
12	Activity K	Class Y	Class Z	Class Y, Class Z
Activities

Cell Formulas
Range	Formula
E2:E11	E2	=IF(B2&C2="",A2,B2&C2)
D2:D12	D2	=IF(B2&C2="",A2,TEXTJOIN(", ",1,B2:C2))

 

[Boubiche]

@Peter_SSs You are priceless.

i have updated my profile and your options worked perfectly!

Many thanks.. i am now able to work on the report and push it to the audience!

thank you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your profile.