• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

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
0
justearth
Asked:
justearth
  • 3
  • 2
2 Solutions
 
Patrick MatthewsCommented:
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
        .[a1].Sort Key1:=.[a1], Order1:=xlAscending, Header:=xlYes
        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)
        Set DestWs = ThisWorkbook.Worksheets.Add
        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

Open in new window

0
 
justearthAuthor 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
 
Patrick MatthewsCommented:
If you change:

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

Open in new window


to:

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

Open in new window


then the labels will inherit whatever names you provide in your data worksheet.
0
 
justearthAuthor Commented:
Please excuse the delay.
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
 
justearthAuthor Commented:
Thanks again.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now