Public additionalConst As String
Public additionalHeader As String
Public targetCol As Integer


Sub AddConstraintsWilcoxon()

    Dim Target As String
    Dim HeaderY As String
    Dim ListY(1 To 2) As String
    Dim HeaderX() As String
    Dim ListX() As String
    Dim temp As String
    Dim shName As String

    Dim i, j, k As Integer
    Dim flag As Integer
    Dim numC As Integer
    Dim minPopulation As Integer
    Dim lastColumn As Integer
    Dim lastRow As Long
    'Dim targetCol As Integer
    Dim colCond As Integer
    Dim colList() As Integer
    Dim uniCol As Integer
    Dim colNo As Integer

    Set WRS = ActiveWorkbook.Worksheets("Wilcoxon_Rank_Sum")
    Set MD = ActiveWorkbook.Worksheets("MetaData")
    Set UT = ActiveWorkbook.Worksheets("UniqueTable")

    'Getting sheet values
    Target = WRS.Cells(2, 2).Value
    HeaderY = WRS.Cells(2, 8).Value
    ListY(1) = WRS.Cells(3, 8).Value
    ListY(2) = WRS.Cells(4, 8).Value
    minPopulation = WRS.Cells(11, 2).Value
    numC = WRS.Cells(10, 2).Value

    'Getting constraints from sheet
    ReDim HeaderX(numC)
    ReDim ListX(numC)
    ReDim colList(numC)

    For i = 1 To numC
        HeaderX(i) = WRS.Cells(i + 1, 4).Value
        ListX(i) = WRS.Cells(i + 1, 6).Value
    Next i

    lastColumn = MD.Cells(1, Columns.Count).End(xlToLeft).Column
    'lastRow = MD.Cells(Rows.Count, 1).End(xlUp).Row

    'Getting column numbers for target and headerY columns
    For i = 1 To lastColumn
        temp = MD.Cells(1, i).Value
        If temp = Target Then
            targetCol = i
        ElseIf temp = HeaderY Then
            colCond = i
        End If
    Next i

    uniCol = UT.Cells(1, Columns.Count).End(xlToLeft).Column

    tableNum = 1

    For i = 1 To 3 'uniCol
        flag = 1
        additionalHeader = UT.Cells(1, i).Value
        If additionalHeader <> HeaderY Then
            For j = 1 To numC
                If additionalHeader = HeaderX(j) Then
                    flag = 0
                    Exit For
                End If
            Next j

            If flag = 1 Then
                Sheets.Add After:=Sheets(Sheets.Count)
                shName = "AdditionalTable-" & additionalHeader
                ActiveSheet.Name = shName
                Set AT = ActiveWorkbook.Sheets(shName)
                ReDim Preserve nameArr(tableNum)
                nameArr(tableNum) = shName
                tableNum = tableNum + 1

                colNo = WorksheetFunction.Match(additionalHeader, MD.Rows(1), 0)
                lastRow = UT.Cells(Rows.Count, i).End(xlUp).Row

                'Iterating through each unique categorical variables in column
                For j = 2 To lastRow
                    additionalConst = UT.Cells(j, i).Value
                    Call WilcoxonRankCalculation(colNo)
                Next j
            End If
        End If
    Next i


End Sub


Sub WilcoxonRankCalculation(colNo As Integer)

    Dim lastRow As Long
    Dim lastColumn As Integer
    Dim rIter As Range
    Dim condStr As String
    Dim nValue(1 To 3) As Long

    'Filtering for additional constraint
    MD.Activate
    MD.AutoFilterMode = False

    lastRow = MD.Cells(Rows.Count, 1).End(xlUp).Row
    lastColumn = MD.Cells(1, Columns.Count).End(xlToLeft).Column

    condStr = "=" & Trim(additionalConst)
    MD.Cells.AutoFilter Field:=colNo, Criteria1:=condStr

    nValue(3) = Application.WorksheetFunction.Subtotal(103, MD.Columns(targetCol)) - 1

    If nValue(3) > 0 Then
        MD.Cells(1, lastColumn + 1).Value = "Rank - " & additionalConst
        Set visRange = MD.Range(Cells(2, targetCol), Cells(lastRow, targetCol)).SpecialCells(xlCellTypeVisible)

        For Each rIter In visRange
            If IsNumeric(rIter.Value) Then
                MD.Cells(rIter.Row, lastColumn + 1).Value = Application.WorksheetFunction.Rank_Avg(rIter.Value, MD.Columns(targetCol).SpecialCells(xlCellTypeVisible), 1)
            End If
        Next rIter
    End If

End Sub