Concatenate data from multiple columns with if statement

Anne-Solene Monrouzeau

Hi,

Does anyone know how to build a formula that would look if the Acct Management / Acturial / Other Dpts columns are checked, and if they are concatenate the name of the SME into one cell?

Genevieve P.

Hi @Anne-Solene Monrouzeau

Yes it's possible! Can you be more specific with the criteria and what cells you're looking to join together?

For example, do you want the cells to be joined only if all three boxes are checked, or if any one is checked?

If it's all three, you would use an IF(AND statement, like so:

=IF(AND([Acct Management]@row = 1, Acturial@row = 1, [Other Dpts]@row = 1),

Then you can join cells together with the + symbol, and any other data you want in between:

[First Name]@row + " " + [Last Name]@row

or

[Last Name]@row + ", " +[First Name]@row

For a full formula of:

=IF(AND([Acct Management]@row = 1, Acturial@row = 1, [Other Dpts]@row = 1), [Last Name]@row + ", " + [First Name]@row)

Let me know if that makes sense and will work for you!

Cheers,

Genevieve

Anne-Solene Monrouzeau

Hi Genevieve,

Let me play with the formula. I think you pointed me in the right direction. I'll let you know if I have questions!

Anne-Solene Monrouzeau

Hi,

I played with the formula and what I need is the list of SME for each departments for which the check box is checked. Right now, it only pulls names if all boxes are checked.

Below is an example:

• if Acct Mgt is checked and actuarial is checked, then pull Acct Mgt and Actuarial SME
• if Acct Mgt is checked and Actuarial is not checked, then pull Acct Mgt SME

How can I do this without building all combinations possible knowing I have 10+ departments?

Genevieve P.

Hi @Anne-Solene Monrouzeau

Thanks for explaining your process & sheet further! What I would do in this instance is have 1 separate IF statement for each possibility, then add all IF statements together with a + sign, like so:

=IF([Acct Management]@row = 1, [Account Management SME]@row + CHAR(10))

The CHAR(10) at the end will create a Line Break after the value it's pulling in. Then + and add the next IF:

=IF([Acct Management]@row = 1, [Account Management SME]@row + CHAR(10)) + IF(Actuarial@row = 1, [Actuarial SME]@row + CHAR(10))

Then you can use wrap text on the cell so each value appears after one after the other. Let me know if this works!

Cheers,

Genevieve

Anne-Solene Monrouzeau

It works!

Last question: The column where the formula is built is a column type, same for all the columns where the SME info are stored. However, the formula returns text. Is there a way with the formula to keep the contact info? That would help us building notifications.

Genevieve P.

Hi @Anne-Solene Monrouzeau

No, there currently isn't a way to join together multiple, separate contacts into one cell and have them appear as a Contact value in a multi-contact column. You would need to have all the possible selections already in a cell and then you could copy that, but a formula can't join contacts in multiple cells together.

Would it always be the same people in your selections? Another option may be to have a reference sheet with all possible combinations, then use this to bring back the correct contact combination based on what's selected in this sheet. Let me know if this makes sense and I'd be happy to help with the cross-sheet formula as well.

Cheers,

Genevieve