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

End If

i = i + 1

Loop

out = Left(out, Len(out) - Len(sep))

CCARRAY = out

Exit Function

EH:

rra = rr.Value

Resume Next

End Function

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.