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.

is it possible to add a new risk row in your excel matrix worksheet, or this is limited to nine risk rows?

You may add as many rows as you please. However, you need to change $C$10 and the other references to the 10th row to for instance 100. Its been a while since I worked with this spreadsheet but let me know if you are unable to fix it and I’ll look into it if you want.