# Conditional concatenation in Excel

So here's a thing I needed to do in Excel. I received a table in a task at work, with a matrix of different user groups and their access rights to different parts of our app. If I simplify it and remove all sensitive data, it looked like this:

To simplify coding I wanted a column where all relevant roles would be listed, ready for copy-pasting into the code. So here's how I went about doing this.

### Firstly

I created a separate row under the group names, to remove unnecessary spaces and dashes with the following formula: `=SUBSTITUTE(SUBSTITUTE(H6;" ";"");"-";"")`, receiving this:

And the most exciting part was learning to use array formulas and `TEXTJOIN` to create conditioned concatenations, building strings with groups that I needed. The key to understanding this is the following: `IF` can receive ranges in its arguments when used in an array formula. To create an array formula, instead of confirming the formula entry with `Return`, press `Ctrl+Shift+Return` (on a Mac), which results in the formula getting enclosed in curly brackets.

The bad thing is you cannot use `AND()` in array functions, so instead of `AND()` you have to use nested `IF()s`. So, to filter out blanks, n/a's and no accesses we need the following: `=IF(NOT(H8:L8="n/a");IF(NOT(H8:L8="no access");IF(NOT(H8:L8="");\$H\$7:\$L\$7;"");"");"")`. It checks the range for `n/a`, and if it is not found, checks for `no access`, if that is not found either, it checks for a blank. If none of those three is found, it references the corresponding cell in the row with group names with removed spaces through `\$H\$7:\$L\$7`, where `\$` means that I always want to reference that range, even when copy-pasting the formula.

This won't work yet though. To finish off the formula, we have to use `TEXTJOIN()`. It accepts 3+ arguments, where the first one is a delimiter, second is a boolean that determines whether blanks have to be concatenated or not, and the other arguments are arrays that have to get concatenated. So, we have to enclose our large `IF()` into `TEXTJOIN(", ", TRUE, IF)`, creating this beauty: `=TEXTJOIN(", ", TRUE, IF(NOT(H8:L8="n/a");IF(NOT(H8:L8="no access");IF(NOT(H8:L8="");\$H\$7:\$L\$7;"");"");""))`. Don't forget to confirm with a `Ctrl+Shift+Return` press:

### Secondly

Then I realized that I don't really need the row where I substituted out all those spaces and dashes, and I incorporated the `SUBSTITUTE()` into `TEXTJOIN()`, resulting in this: `=TEXTJOIN(", ", TRUE, IF(NOT(H8:L8="n/a");IF(NOT(H8:L8="no access");IF(NOT(H8:L8="");SUBSTITUTE(SUBSTITUTE(\$H\$7:\$L\$7;" ";"")"-";"");"");"");""))` Another press of `Ctrl+Shift+Return`, and we're golden.