Using an array to manipulate and standardize data

[Waimea]

Hi, I am working with tables in Excel and I am trying to automatically load the multicolumn table into an array,

I then want to calculate the AVERAGE for each table list column, the STDEV.P for each table list column and finally STANDARDIZE the data using the AVERAGE and STDEV.P I calculated earlier.

I think I understand how to load the table into an array but now how to actually loop through the data and manipulate it?

How can I loop through all list columns and calculate the AVERAGE for that list column, calculate the STDEV.P for that list column and finally calculate the STANARDIZE score?

Sub ArrayToStandardize()


Dim TableToArray As ListObject
Dim NewArray As Variant
Dim x As Long
Dim R As Long


ReDim NewArray(1 To 3, 1 To 100)


Set TableToArray = Sheets("Test1").ListObjects("Table1") 'Set path for Table variable
NewArray = TableToArray.DataBodyRange 'Create Array List from Table
For x = LBound(NewArray) To UBound(NewArray)
    
    NewArray(x, 1) = WorksheetFunction.Average(x, 1)
           
    'NewArray(x, 2) = WorksheetFunction.Average(x, 2)
    
    Debug.Print NewArray(x, 2)
    'Debug.Print NewArray(x, 2)
    'Debug.Print NewArray(x, 3) 
Next x
        
R = Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Test1").Cells(2, 5).Resize(UBound(NewArray, 1), UBound(NewArray, 2)) = NewArray
  
End Sub

 

[Waimea]

I have tried both, neither works at the moment in my code.

I found this code but I am unsure how it works with arrays, what would be my select case for the array with headers?

Origin = .Cells(i, OriCol).Value 

Select Case Origin 

Case "CN", "AU", "HK" ,"THA", "VIE", "PHI"
    Region = "Asia" 

Case "DE", "SE", "GB", "ITA", "SPN", "POR", "FRA"
    Region = "Europe" 

Case Is = "US" 
    Region = "US" 

Case Else 

End Select

 

[Waimea]

Could anyone post some more examples of data manipulating with arrays?

 

[steve the fish]

Heres one with a Case statement

Country = "Country"
Region = "Region"

With Sheets("Sheet3").ListObjects("Table1")
    CountryCol = Application.Match(Country, .HeaderRowRange, 0)
    RegionCol = Application.Match(Region, .HeaderRowRange, 0)
    CountryArr = .ListColumns(CountryCol).DataBodyRange
    RegionArr = .ListColumns(RegionCol).DataBodyRange
End With

For i = LBound(CountryArr, 1) To UBound(CountryArr, 1)
    Select Case UCase(CountryArr(i, 1))
        Case "CN", "AU", "HK", "THA", "VIE", "PHI"
            RegionArr(i, 1) = "Asia"
        Case "DE", "SE", "GB", "ITA", "SPN", "POR", "FRA"
            RegionArr(i, 1) = "Europe"
        Case "US"
            RegionArr(i, 1) = "US"
        Case Else
    End Select
Next

Sheets("Sheet3").ListObjects("Table1").ListColumns(RegionCol).DataBodyRange = RegionArr

 

[Waimea]

Thank you for your example steve the fish!

However, I am not sure what do with Country = "Country" and Region = "Region"?

 

[steve the fish]

They are just variables where your headers with the country codes in is called 'Country' and the column where you want the region filling in for the corresponding country is called 'Region'. Place this in Sheet3:

Region	Country
	US
	GB
	CN

<colgroup><col span="2"></colgroup><tbody>
</tbody>

Make it into a table called Table1 with headers. Run the macro and the region column will get filled in.

 

[Waimea]

I got it to work with your instructions steve the fish and I think I understand how it works!

Thank you!

 

[Waimea]

So how would I loop through an array with column names and calculate the average, the standard deviation and finally standardize each column?

Sub Standardize()


'Dim ws1 As Worksheet
'Set ws1 = ThisWorkbook.worksheets("Snabb2")
'ws1.Copy ThisWorkbook.Sheets(Sheets.Count)


With Sheets("Snabb2")
.Cells(1, "U") = "MEAN"
.Cells(2, "U") = "STDEV"


.range("V1:V1").Formula = "=AVERAGE(B2:B41)"
.range("W1:W1").Formula = "=AVERAGE(C2:C41)"
.range("X1:X1").Formula = "=AVERAGE(D2:D41)"
.range("Y1:Y1").Formula = "=AVERAGE(E2:E41)"
.range("Z1:Z1").Formula = "=AVERAGE(F2:F41)"
.range("AA1:AA1").Formula = "=AVERAGE(G2:G41)"
.range("AB1:AB1").Formula = "=AVERAGE(H2:H41)"
       
.range("V2:V2").Formula = "=STDEV.P(B2,V1,V2)"
.range("W2:W2").Formula = "=STDEV.P(C2,W1,W2)"
.range("X2:X2").Formula = "=STDEV.P(D2,X1,X2)"
.range("Y2:Y2").Formula = "=STDEV.P(E2,Y1,Y2)"
.range("Z2:Z2").Formula = "=STDEV.P(F2,Z1,Z2)"
.range("AA2:AA2").Formula = "=STDEV.P(G2,X1,X2)"
.range("AB2:AB2").Formula = "=STDEV.P(H2,Y1,Y2)"
                  
.range("I2:I41").Formula = "=STANDARDIZE(B2,V$1,V$2)"
.range("J2:J41").Formula = "=STANDARDIZE(C2,W$1,W$2)"
.range("K2:K41").Formula = "=STANDARDIZE(D2,X$1,X$2)"
.range("L2:L41").Formula = "=STANDARDIZE(E2,Y$1,Y$2)"
.range("M2:M41").Formula = "=STANDARDIZE(F2,Z$1,Z$2)"
.range("N2:N41").Formula = "=STANDARDIZE(G2,X$1,X$2)"
.range("O2:O41").Formula = "=STANDARDIZE(H2,Y$1,Y$2)"


End With


End Sub

At the moment I am adding these formulas manually!

 

[Waimea]

Perhaps there is a way to loop the ranges used in the formulas?

 

[steve the fish]

I dont know if you mean this or not as you have gone in many different directions through this thread but try:

.Range("V1:AB1").Formula = "=AVERAGE(B2:B41)"
.Range("V2:AB2").Formula = "=STDEV.P(B2,V1,V2)"
.Range("I2:O41").Formula = "=STANDARDIZE(B2,V$1,V$2)"

 

[Waimea]

Hi steve the fish,

thank you for your reply. I am sorry for going in different directions. Your code does what I am trying to do.