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.
