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.
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
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
$ 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
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
Then I realized that I don't really need the row where I substituted out all those spaces and dashes, and I incorporated the
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.