Assigning weights to certain types of records from a list of weights without just copying and pasting

[JulianJohannesen]

Hello all,

I have about 29,000 records with all sorts of demographic data for individuals. These records each belong to one of 13 states or counties, 5 ethnic categories and 3 age categories. I want to assign a specific weight to each record in a category. I have my list of 13x5x3 = 195 weights. How can can assign those weights to my records without performing 195 copy and paste operations and then copying that weight factor down for the number of records in each of those categories?

[TABLE="width: 291"]
<tbody>[TR]
[TD="colspan: 4"]A unique weight goes with each category[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Race[/TD]
[TD]Age[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]18-29[/TD]
[TD]weight1[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]30-59[/TD]
[TD]weight2[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]60+[/TD]
[TD]weight3[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]black[/TD]
[TD]18-29[/TD]
[TD]weight4[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]black[/TD]
[TD]30-59[/TD]
[TD]weight5[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]black[/TD]
[TD]60+[/TD]
[TD]weight6[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]caucasian[/TD]
[TD]18-29[/TD]
[TD]weight7[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]caucasian[/TD]
[TD]30-59[/TD]
[TD]weight8[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]caucasian[/TD]
[TD]60+[/TD]
[TD]weight9[/TD]
[/TR]
[TR]
[TD]… etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Actual data without weight included[/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Race[/TD]
[TD]Age[/TD]
[TD]Weight[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]18-29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]18-29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]18-29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]18-29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]18-29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]18-29[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]30-59[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]30-60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]30-61[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]30-62[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]30-63[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]30-64[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]30-65[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[TD]asian[/TD]
[TD]30-66[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]… and so on for thousands of records

[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Pivot Tables were created for this purpose, I believe.

 

[igold]

Hi Julian,

This code should do what you would like. I tested it on a small set of data and it works. There is work you are going to have to do to complete it. I tried to include comments to explain what I was doing. If you would like to PM me feel free.
The code also assumes the data is set up exactly as you have shown, starting with "State" in cell A1.
This code is going to write data so please test it on a back-up copy of your work!

Sub WhoseWeight()

    Dim lRow As Long, a As Long, s As Long, e As Long
    Dim state(1 To 13) As String
    Dim ethnic(1 To 5) As String
    Dim age(1 To 3) As String
    Dim wn As Long

    age(1) = "18-29"
    age(2) = "30-59"
    age(3) = "60+"
'====================You have to correct your states==============
    state(1) = "AZ"
    state(2) = "NM"
    state(3) = "NY"
    state(4) = "NJ"
    state(5) = "CA"
    state(6) = "FL"
    state(7) = "TX"
    state(8) = "OK"
    state(9) = "NV"
    state(10) = "WV"
    state(11) = "GA"
    state(12) = "OH"
    state(10) = "AK"
    state(11) = "NC"
    state(12) = "VT"
    state(13) = "MA"
'=====================You have to correct your ethnicities===================
    ethnic(1) = "black"
    ethnic(2) = "caucasian"
    ethnic(3) = "asian"
    ethnic(4) = "mexican"
    ethnic(5) = "persian"
    

    lRow = Range("A" & Rows.Count).End(xlUp).Row
    For a = 1 To 3
        For s = 1 To 13
            For e = 1 To 5
'======================You will have to finish adding your if designations below for the weight categories- All 165 of them
'                                        Just put in the number part of the weight as "wn = ??"
'                                        The word "Weight" will appear from the code below.
'                                        Following along from above:  a3/s10/e4 would be a 60+ / West Virginia / Mexican

            If a = 1 And s = 1 And e = 1 Then wn = 1   ' this is a1/s1/e1 = Weight1
            If a = 1 And s = 1 And e = 2 Then wn = 2   ' this is a1/s1/e2 = Weight2
            If a = 1 And s = 1 And e = 3 Then wn = 3
            If a = 1 And s = 1 And e = 4 Then wn = 4
            
            With Worksheets("Sheet1")
                .AutoFilterMode = False
       
                With .Range("A1:D" & lRow)
                    .AutoFilter field:=1, Criteria1:=state(s)
                    .AutoFilter field:=2, Criteria1:=ethnic(e)
                    .AutoFilter field:=3, Criteria1:=age(a)
                    .SpecialCells (xlCellTypeVisible)
                    .SpecialCells(xlCellTypeBlanks).Value = "Weight" & wn
                End With
            .AutoFilterMode = False
            End With
            Next
        Next
    Next
    
End Sub

HTH

igold