# Create Correlation Matrices based on Groups (CORRELATE)

Hello,

I would like to compute correlation matrices (using CORRELATE) for each group in a worksheet. The correlation matrices should be by group (5,6,7,12,13, or 14) on columns D - H.  Ideally the new sheet would be named with at least the group number.

I have attached an example output for group 5. I also included a screen cast with an example worksheet of how I would do this in a non automated way.

I am looking for an automated way to do this (i.e. macro, formula). Annotation always appreciated.

Thank you kindly,
JE
justearth-457244.flv
JE-EE-corr-matrix-by-group.xlsx
###### Who is Participating?

Commented:
Assuming that your data columns are always in D:H, this seems to work.

The part where I put in the formulae could probably be done more elegantly :)

``````Sub MakeMatrix()

Dim arr() As Long
Dim CurrentGroup As Long
Dim SourceWs As Worksheet
Dim LastR As Long
Dim Counter As Long
Dim DestWs As Worksheet

Set SourceWs = ThisWorkbook.Worksheets("data")
With SourceWs
LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
CurrentGroup = .Cells(2, 1)
ReDim arr(1 To 3, 1 To 1) As Long
arr(1, 1) = CurrentGroup
arr(2, 1) = 2
For Counter = 3 To LastR
If .Cells(Counter, 1) <> CurrentGroup Then
arr(3, UBound(arr, 2)) = Counter - 1
ReDim Preserve arr(1 To 3, 1 To 1 + UBound(arr, 2)) As Long
arr(1, UBound(arr, 2)) = .Cells(Counter, 1)
arr(2, UBound(arr, 2)) = Counter
CurrentGroup = .Cells(Counter, 1)
ElseIf Counter = LastR Then
arr(3, UBound(arr, 2)) = Counter
End If
Next
End With

For Counter = 1 To UBound(arr, 2)
With DestWs
.Name = "Group " & arr(1, Counter) & " Matrix"
.[a2:a6] = Application.Transpose(Array("Area1", "Area2", "Area3", "Area4", "Area5"))
.[b1:f1] = Array("Area1", "Area2", "Area3", "Area4", "Area5")
.Range("b2, c3, d4, e5, f6") = 1
.[b3].Formula = "=CORREL(Data!D" & arr(2, Counter) & ":D" & arr(3, Counter) & _
",Data!E" & arr(2, Counter) & ":E" & arr(3, Counter) & ")"
.[b4].Formula = "=CORREL(Data!D" & arr(2, Counter) & ":D" & arr(3, Counter) & _
",Data!F" & arr(2, Counter) & ":F" & arr(3, Counter) & ")"
.[b5].Formula = "=CORREL(Data!D" & arr(2, Counter) & ":D" & arr(3, Counter) & _
",Data!G" & arr(2, Counter) & ":G" & arr(3, Counter) & ")"
.[b6].Formula = "=CORREL(Data!D" & arr(2, Counter) & ":D" & arr(3, Counter) & _
",Data!H" & arr(2, Counter) & ":H" & arr(3, Counter) & ")"
.[c4].Formula = "=CORREL(Data!E" & arr(2, Counter) & ":E" & arr(3, Counter) & _
",Data!F" & arr(2, Counter) & ":F" & arr(3, Counter) & ")"
.[c5].Formula = "=CORREL(Data!E" & arr(2, Counter) & ":E" & arr(3, Counter) & _
",Data!G" & arr(2, Counter) & ":G" & arr(3, Counter) & ")"
.[c6].Formula = "=CORREL(Data!E" & arr(2, Counter) & ":E" & arr(3, Counter) & _
",Data!H" & arr(2, Counter) & ":H" & arr(3, Counter) & ")"
.[d5].Formula = "=CORREL(Data!F" & arr(2, Counter) & ":F" & arr(3, Counter) & _
",Data!G" & arr(2, Counter) & ":G" & arr(3, Counter) & ")"
.[d6].Formula = "=CORREL(Data!F" & arr(2, Counter) & ":F" & arr(3, Counter) & _
",Data!H" & arr(2, Counter) & ":H" & arr(3, Counter) & ")"
.[e6].Formula = "=CORREL(Data!G" & arr(2, Counter) & ":G" & arr(3, Counter) & _
",Data!H" & arr(2, Counter) & ":H" & arr(3, Counter) & ")"
End With
Next

MsgBox "Done"

End Sub
``````
0

Author Commented:
Hello,
Thanks.

I have just a couple questions.

In lines 35 and 36, whats the best way to change the names of these? My actual data will use different a different sheet? Did you type them in, or is there a different way to do that?

If I want to change the columns is there a preferred way to do that?

Thanks again,
JE
0

Commented:
If you change:

``````            .[a2:a6] = Application.Transpose(Array("Area1", "Area2", "Area3", "Area4", "Area5"))
.[b1:f1] = Array("Area1", "Area2", "Area3", "Area4", "Area5")
``````

to:

``````            .[a2:a6] = Application.Transpose(SourceWs.[d1:h1])
.[b1:f1].Value = SourceWs.[d1:h1].Value
``````

then the labels will inherit whatever names you provide in your data worksheet.
0

Author Commented:
matthewspatrick - this is great thanks.

One last time:
Also, is there a "sweet" way to select the columns used? To make this script more adaptable?

Cheers,
Thank you kindly,
JE
0

Author Commented:
Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.