# Automatically updated risk matrix in Excel

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.  