For some time, I have wanted to make an automatically updated risk matrix in Excel. The matrix should automatically place the risk ID in the right cell of the matrix and show the current trend of the risk. After getting a risk responsibility in a new project I did a few web searches and came across this script that puts a series of values into a single cell (thanks Excelll).
I did a few modifications to the script to fit it to my needs.
Public Function CCARRAY(rr As Variant, sep As String)
'rr is the range or array of values you want to concatenate. sep is the delimiter.
Dim rra() As Variant
Dim out As String
Dim i As Integer
On Error GoTo EH
rra = rr
out = ""
i = 1
Do While i <= UBound(rra, 1)
If ((rra(i, 1) <> False) And (Len(rra(i, 1)))) Then
out = out & rra(i, 1) & sep
i = i + 1
out = Left(out, Len(out) - Len(sep))
CCARRAY = out
rra = rr.Value
Then I created this Risk example as Excel spreadsheet and stored it as an Macro-Enabled Workbook. The cells in the risk matrix contains a formula like this:
=IFERROR(CCARRAY(IF($B3=RiskList!$C$2:$C$10;IF(C$2=RiskList!$D$2:$D$10;RiskList!$A$2:$A$10&" ("&RiskList!$F$2:$F$10&") ";"");""); ", "); "")
Remember to press “Shift+Ctrl+Enter” when exiting the cell to get the matrix functions working . The “beautiful” result may be viewed below.