SLOW VBA (20+ minutes) with many loops and VLOOKUPS. Help please.

**Supersipe** · 05-25-2022, 02:37 AM

Alcon,

BLUF:
I am requesting help on the below code. The key things I am requesting help:

1. Speed it up. It is OMG slow. (at least 20-30 minutes and not all the way finished)
2. I believe the use of Dictionary/Array's like Ubound would work way quicker.
3. General cleanup of the code (if need be).

Background:
I have written over the past month or so. Yes, I'm a noob. I have come to the forums, YouTube and what you see is what I have taught myself over the past month or so. But I'm stuck. In my mind, some of it is complex because for my use in the Army, I am pulling Civilians, local nationals and all ranks within the Army. The issue is that I am pulling from 4 different data sheets (DTMS Roster, DTMS Training, DTMS ACFT, DTMS Weapons). Within those 4 sheets, I have to extract the data and pull it to Master Data sheet. I KNOW there is a quicker way, but I am truly at a loss now and don't know where to turn. Any help would be GREATLY appreciated.

here is the code


' MasterData Macro
'
    'DON'T FORGET TO EXEMPT THE RANK/MOST FROM ICTL'S
        
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    MsgBox ("Hang tight. Your data is now being compiled.")
    
    Dim lRow As Integer 'variable for last row of column
    Dim lRowU As Integer
    Dim lRowT As Integer
    Dim lRowR As Integer
    Dim weplRow As Integer
    Dim edipi As String
    Dim rankNo As Integer
    Dim ictlCount As Integer
    Dim found As Integer
    Dim rg As Range
    Dim shMaster As Worksheet
    Dim shRoster As Worksheet
    Dim shTraining As Worksheet
    Dim shWeapons As Worksheet
    Dim shICTL As Worksheet
    Dim shConversion As Worksheet
        
    'Pull DTMS Roster Rank No, Main Unit, and Last, First
   
    lRowT = Worksheets("DTMS Training").Range("A1").End(xlDown).Row
    lRowR = Worksheets("DTMS Roster").Range("B1").End(xlDown).Row
    
    Set shMaster = Worksheets("Master Data")
    Set shRoster = Worksheets("DTMS Roster")
    Set shTraining = Worksheets("DTMS Training")
    Set shWeapons = Worksheets("DTMS Weapons")
    Set shICTL = Worksheets("ICTL Data Table")
    Set shConversion = Worksheets("Conversion Files")
    
    'Clear any Data
    shMaster.Range("A1").CurrentRegion.Offset(1).ClearContents
    
    'Last row but also vlookups and conversion to values in Roster to paste over to Master Data
    Worksheets("DTMS Roster").Select
    Range("A1").Select
    'Worksheets("DTMS Roster").AutoFilter.Sort.SortFields.Clear
    'Worksheets("DTMS Roster").AutoFilter.Sort.SortFields.Add Key:= _
        'Range("H1:H670"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        ':=xlSortNormal
    
    'With ActiveWorkbook.Worksheets("DTMS Roster").AutoFilter.Sort
    '    .Header = xlYes
    '    .MatchCase = False
    '    .Orientation = xlTopToBottom
    '    .SortMethod = xlPinYin
    '    .Apply
    'End With
    
    shRoster.Range("G1").Value2 = "Rank No"
    Range("H1").Value2 = "Main Unit"
    Range("I1").Value2 = "Last, First"
    Range("G2").Value2 = "=IFERROR(VLOOKUP(C2,'Conversion Files'!H:I,2,FALSE),"""")"
    Range("H2").Value2 = "=IFERROR(VLOOKUP(D2,'Conversion Files'!A:B,2,FALSE),"""")"
    Range("I2").Value2 = "=PROPER(B2)"
    Range("G2:I2").Select
    Selection.AutoFill Destination:=Range("G2:I" & lRowR)
    Columns("G:I").Copy
    Columns("G:I").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A2").Select
    
    'Pull DTMS Roster Rank No, Main Unit, and Last, First
    lRowR = Worksheets("DTMS Roster").Range("B1").End(xlDown).Row
    lRowT = Worksheets("DTMS Training").Range("A1").End(xlDown).Row
    
    'DTMS Training vlookup for last 3 columms (V, W, X) and convert to value to ensure lRow is completed. this _
    avoids extra cells in Master Data
    shTraining.Select
    Range("V2").Value2 = "Skill Level"
    Range("W2").Value2 = "Loop Numerator" 'this is the MOS finder associated with skill level
    Range("X2").Value2 = "UnitEasy"
    Range("V2").Value2 = "=IFERROR(VLOOKUP(RC[-4],'ICTL Data Table'!C[-20]:C[-19],2,FALSE),"""")"
    Range("W2").Value2 = "=IFERROR(VLOOKUP(C[-5],'ICTL Data Table'!C[-21]:C[-19],3,FALSE),"""")"
    Range("X2").Value2 = "=IFERROR(VLOOKUP(C[-12],'Conversion Files'!C[-23]:C[-22],2,FALSE),"""")"
    Range("V2:X2").Select
    Selection.AutoFill Destination:=Range("V2:X2" & lRowT)
    Columns("V:X").Copy
    Columns("V:X").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A2").Select
   
    'Select Master Data sheet to begin copying all data and make magic happen
    
    Worksheets("Master Data").Select
    Range("A2").Select
     
    ' AdvFilter
    Set shRoster = Worksheets("DTMS Roster")


    'Super Copy Code


    Set rg = shRoster.Range("A1").CurrentRegion
    
    
    'EPIDI
    Set criteriaRange = Worksheets("AdvFilter").Range("A1").CurrentRegion
    
    rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("A1")
    
    'Last, First
    Set criteriaRange = Worksheets("AdvFilter").Range("C1").CurrentRegion
    
    rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("B1")
    
    'Rank
    Set criteriaRange = Worksheets("AdvFilter").Range("E1").CurrentRegion
    
    rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("C1")
    
    'MOS
    Set criteriaRange = Worksheets("AdvFilter").Range("G1").CurrentRegion
    
    rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("D1")
    
    'Unit
    Set criteriaRange = Worksheets("AdvFilter").Range("I1").CurrentRegion
    
    rg.AdvancedFilter xlFilterCopy, criteriaRange, shMaster.Range("E1")
    
    lRow = Worksheets("Master Data").Range("B1").End(xlDown).Row
    
    'With Columns A:Z
    
    With Columns("A:A")  'EDIPI Column
        .HorizontalAlignment = xlLeft
        .ColumnWidth = 11
        With .Font
            .Name = "Arial"
            .Size = 10
            .Bold = False
        End With
    End With
    
    With Columns("B:B") 'Last Column
        .HorizontalAlignment = xlLeft
        .ColumnWidth = 30
        With .Font
            .Name = "Arial"
            .Size = 10
            .Bold = True
        End With
    End With
    
    With Columns("C:D") 'Rank/MOS Column
        .HorizontalAlignment = xlLeft
        .ColumnWidth = 6
        With .Font
            .Name = "Arial"
            .Size = 10
            .Bold = True
        End With
    End With
    
    With Columns("E:E") 'Main Unit Column
        .HorizontalAlignment = xlLeft
        .ColumnWidth = 13
        With .Font
            .Name = "Arial"
            .Size = 10
            .Bold = True
        End With
    End With
    
    With Columns("F:F") 'ACFT Date Column
        .HorizontalAlignment = xlCenter
        .ColumnWidth = 12
        .NumberFormat = "dd mmm yyyy"
        With .Font
            .Name = "Arial"
            .Size = 10
            .Bold = True
        End With
    End With
        


    With Columns("G:H") 'ACFT Score/Weapon Type Column
        .HorizontalAlignment = xlCenter
        .ColumnWidth = 8
        .NumberFormat = "0"
        With .Font
            .Name = "arial"
            .Size = 10
            .Bold = True
        End With
    End With
      
    With Columns("I:I")  'Weapon Date Column
        .HorizontalAlignment = xlCenter
        .ColumnWidth = 12
        .NumberFormat = "dd mmm yyyy"
        With .Font
            .Name = "arial"
            .Size = 10
            .Bold = True
        End With
    End With
    
    With Columns("J:K") 'ICT Completed/Assigned Column
        .HorizontalAlignment = xlCenter
        .ColumnWidth = 9
        .NumberFormat = "0"
        With .Font
            .Name = "arial"
            .Size = 10
            .Bold = True
        End With
    End With
    
    lRow = Range("B1").End(xlDown).Row
    
    With Columns("M:Z") '350-1 Training (Blue/Orange Training)
        .HorizontalAlignment = xlCenter
        .ColumnWidth = 12
        .NumberFormat = "dd mmm yyyy"
        With .Font
            .Name = "arial"
            .Size = 10
            .Bold = True
        End With
    End With
      
    'With Ranges (Headers A:Z)
    
    With Range("A1:E1") 'EDIPI Header
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
    End With
    
    With Range("F1:L1") 'ACFT Date thru ICT % Alignment
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
    End With
    
    With Range("F1")
        .Value2 = "ACFT       Date"
    End With
    
    With Range("G1")  'ACFT Score Header
        .Value2 = "ACFT     Score"
    End With
    
    With Range("H1") 'Weapon Type Header
        .Value2 = "Weapon Type"
    End With


    With Range("I1")  'Weapon Date Header
        .Value2 = "Weapon Date"
    End With


    With Range("J1") 'ICT Completed Headerw
        .Value2 = "ICT Completed"
    End With
    
    With Range("K1")  'ICT Assigned Header
        .Value2 = "ICT   Assigned"
    End With


    With Range("L1")  'ICT % Complete Header
        .Value2 = "ICT % Complete"
    End With


    With Range("M1") 'EO Header
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .Value2 = "EO"
    End With
    
    With Range("N1") 'Resilience Header
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .Value2 = "Resilience"
    End With


    With Range("O1") 'Personal Readiness Header
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .Value2 = "Personal Readiness"
    End With


    With Range("P1:S1") 'INFOSEC Header
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
    End With
    
    With Range("P1")
        .Value2 = "INFOSEC"
    End With
    
    With Range("Q1") 'OPSEC Header
        .Value2 = "OPSEC"
    End With
    
    With Range("R1") 'SHARP Header
        .Value2 = "SHARP"
    End With
    
    With Range("S1") 'AT Lvl 1 Header
        .Value2 = "AT Lvl 1"
    End With
    
    With Range("T1") 'Cyber Awareness Header
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .Value2 = "Cyber Awareness"
    End With
    
    With Range("U1:V1") 'HIPAA Header
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
    End With
    
    With Range("U1")
         .Value2 = "HIPAA"
    End With


    With Range("V1") 'TARP Header
        .Value2 = "TARP"
    End With


    With Range("W1:Z1") 'Maintin M4 AWT 1 Header
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
    End With
    
    With Range("W1")
        .Value2 = "Maintin M4 AWT 1"
    End With


    With Range("X1") 'Under Fire  AWT 2 Header
        .Value2 = "Under Fire  AWT 2"
    End With


    With Range("Y1") 'TC3 Card AWT 3 Header
        .Value2 = "TC3 Card AWT 3"
    End With
    
    With Range("Z1") 'SITREP   AWT 4 Header
        .Value2 = "SITREP   AWT 4"
    End With






    'We MAY NOT need this vlookup at all. simply add the exempt to the Cases! saves on a .Select
   'Pull ACFT Date and Score
    'Range("F2").Select
    'Range("F2").Value2 = "=IFERROR(IF(LEFT(C2,1)=""G"",""Exempt"",IF(C2=""CON"",""Exempt"",(VLOOKUP(A2,'DTMS ACFT'!C:K,9,FALSE)))),"""")"
    'Range("G2").Value2 = "=IFERROR(IF(LEFT(C2,1)=""G"",""Exempt"",IF(C2=""CON"",""Exempt"",VLOOKUP(A2,'DTMS ACFT'!C:L,10,FALSE))),"""")"
    'Range("G2:F2").Select
    'Selection.AutoFill Destination:=Range("F2:G" & lRow)
    'Columns("F:G").Copy
    'Columns("F:G").Select
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    '    :=False, Transpose:=False
    'Application.CutCopyMode = False
          
          
          
          
        'Pull Weapon Type
    Range("H2").Value2 = "=IF(IFERROR(VLOOKUP(B2,'Conversion Files'!S:U,3,FALSE),"""")<>""Y"",IF(IFERROR(VLOOKUP(C2,'Conversion Files'!H:I,2,FALSE),"""")<5,""M4"",IF(IFERROR(VLOOKUP(C2,'Conversion Files'!H:I,2,FALSE),"""")<30,""M17"",""Exempt"")),""IMA"")"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H" & lRow)
    Columns("H:H").Copy
    Columns("H:H").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


    'Pull Weapon Date
    weplRow = shWeapons.Range("A1").End(xlDown).Row
    For i = 2 To lRow
        'M17 qual lookup
        If Left(shMaster.Range("C" & i).Value2, 1) = "G" Then shMaster.Range("I" & i).Value2 = "Exempt" 'IF(LEFT(C2,1)=""G"",""Exempt"",IF(C2=""CON"",""Exempt"",
        If shMaster.Range("C" & i).Value2 = "CON" Then shMaster.Range("I" & i).Value2 = "Exempt"
        
        If shMaster.Range("H" & i).Value2 = "M17" Then 'if masterDatatab Gi = "M17" then
            'edipi = Worksheets("Master Data").Range("A" & i).Value 'EDIPI = Ai
            For j = 2 To weplRow
                If shWeapons.Range("E" & j).Value2 = shMaster.Range("A" & i).Value2 Then 'if Weaponstap Ej = EDIPI Then
                    For k = j To j + 10
                        If shWeapons.Range("E" & k).Value2 <> edipi Then
                            k = k + 10
                        End If
                        If shWeapons.Range("P" & k).Value2 = "XM-17 PISTOL, MODULAR" Or shWeapons.Range("P" & k).Value2 = "M9 9mm Beretta Pistol" Then 'if Pk = "M17" or "M9" then
                            shMaster.Range("I" & i).Value2 = shWeapons.Range("T" & k).Value2 'Worksheets("Weapons").Range("T" & k).Value 'MasterDataTab Hi.value = Weaponstab Tk.value
                            k = k + 10
                        End If
                    Next k
                    j = j + weplRow
                End If
            Next j
        
        'else if shMaster.Range("H" & i).Value = "IMA" Then
        
        
        End If
        
        'M4 qual lookup
        If shMaster.Range("H" & i).Value2 = "M4" Then 'if masterDatatab Gi = "M4" then
            edipi = shMaster.Range("A" & i).Value2 'EDIPI = Ai
            For j = 2 To weplRow
                If shWeapons.Range("E" & j).Value2 = edipi Then 'if Weaponstap Ej = EDIPI Then
                    For k = j To j + 10
                        If shWeapons.Range("E" & k).Value2 <> edipi Then
                            k = k + 10
                        End If
                        If Left(Worksheets("DTMS Weapons").Range("P" & k).Value2, 2) = "M4" Then 'if Pk = "M4" then
                            shMaster.Range("I" & i).Value2 = shWeapons.Range("T" & k).Value2 'Worksheets("Weapons").Range("T" & k).Value2 'MasterDataTab Hi.value2 = Weaponstab Tk.value2
                            k = k + 10
                        End If
                    Next k
                    j = j + weplRow
                End If
            Next j
        End If
    Next i


    'ICTL Data
   
    ictlCount = 0


    
    For i = 2 To lRow
        For j = 2 To 42
            If shMaster.Range("C" & i).Value2 = shConversion.Range("H" & j).Value2 Then
                rankNo = shConversion.Range("I" & j).Value2
            j = j + 42
            End If
        Next j
        'For k = 2 To 31
            'If shMaster.Range("D" & i).Value = shICTL.Range("AK" & k).Value Then
                
                Select Case rankNo
                    Case 1, 11, 12, 13, 14, 15
                        For k = 2 To 31
                            If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
                                shMaster.Range("K" & i).Value2 = shICTL.Range("AL" & k).Value2 'denominator
                                k = 31
                            Else
                                If k = 31 Then
                                    shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
                                End If
                            End If
                        Next k
                    
                        For l = 2 To lRowT 'loop to count the numerator and to check for training dates
                            If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
                                found = 1
                                
                                Select Case shTraining.Range("R" & l).Value2
                                    Case "DA-CMT12" 'Resilience
                                        shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT15" 'Personal Readiness
                                        shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT10" 'EO / EEO
                                        shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT18" 'INFOSEC
                                        shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT16" 'OPSEC
                                        shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "150S-SHA-0100" 'SHARP
                                        shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT01" 'AT Lvl 1
                                        shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT19" 'Cyber Awareness
                                        shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "MC - 00020" 'HIPAA
                                        shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT02" 'TARP
                                        shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
                                End Select
                                
                                If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
                                    If shTraining.Range("V" & l).Value2 = 1 Then
                                        ictlCount = ictlCount + 1
                                    End If
                                End If
                            Else
                                If found = 1 Then
                                    l = lRowT
                                    found = 0
                                Else
                                    If l = lRowT Then
                                        shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
                                    End If
                                End If
                            End If
                        Next l
                            If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
                                shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
                                shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
                            Else
                                shMaster.Range("J" & i).Value2 = ictlCount 'numerator
                                ictlCount = 0
                            End If
                    Case 2
                        For k = 2 To 31
                            If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
                                shMaster.Range("K" & i).Value2 = shICTL.Range("AM" & k).Value2 'denominator
                                k = 31
                            Else
                                If k = 31 Then
                                    shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
                                End If
                            End If
                        Next k
                        
                         For l = 2 To lRowT 'loop to count the numerator
                            If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
                                found = 1
                                
                                Select Case shTraining.Range("R" & l).Value2
                                   Case "DA-CMT12" 'Resilience
                                        shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT15" 'Personal Readiness
                                        shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT10" 'EO / EEO
                                        shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT18" 'INFOSEC
                                        shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT16" 'OPSEC
                                        shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "150S-SHA-0100" 'SHARP
                                        shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT01" 'AT Lvl 1
                                        shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT19" 'Cyber Awareness
                                        shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "MC - 00020" 'HIPAA
                                        shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT02" 'TARP
                                        shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
                                End Select
                                
                                If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
                                    If shTraining.Range("V" & l).Value2 < 3 Then
                                        ictlCount = ictlCount + 1
                                    End If
                                End If
                        Else
                                If found = 1 Then
                                    l = lRowT
                                    found = 0
                                Else
                                    If l = lRowT Then
                                        shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
                                    End If
                                End If
                            End If
                        Next l
                            If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
                                shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
                                shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
                            Else
                                shMaster.Range("J" & i).Value2 = ictlCount 'numerator
                                ictlCount = 0
                            End If
 
                    Case 3
                        For k = 2 To 31
                            If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
                                shMaster.Range("K" & i).Value2 = shICTL.Range("AN" & k).Value2 'denominator
                                k = 31
                            Else
                                If k = 31 Then
                                    shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
                                End If
                            End If
                        Next k
 
                        For l = 2 To lRowT 'loop to count the numerator
                            If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
                                found = 1
                                
                                Select Case shTraining.Range("R" & l).Value2
                                    Case "DA-CMT12" 'Resilience
                                        shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT15" 'Personal Readiness
                                        shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT10" 'EO / EEO
                                        shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT18" 'INFOSEC
                                        shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT16" 'OPSEC
                                        shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "150S-SHA-0100" 'SHARP
                                        shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT01" 'AT Lvl 1
                                        shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT19" 'Cyber Awareness
                                        shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "MC - 00020" 'HIPAA
                                        shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT02" 'TARP
                                        shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
                                End Select
                                
                                If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
                                    If shTraining.Range("V" & l).Value2 <= 3 Then
                                        ictlCount = ictlCount + 1
                                    End If
                                End If
                        Else
                                If found = 1 Then
                                    l = lRowT
                                    found = 0
                                Else
                                    If l = lRowT Then
                                        shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
                                    End If
                                End If
                            End If
                        Next l
                            If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
                                shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
                                shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
                            Else
                                shMaster.Range("J" & i).Value2 = ictlCount 'numerator
                                ictlCount = 0
                            End If


                    Case 4, 5
                        For k = 2 To 31
                            If shMaster.Range("D" & i).Value2 = shICTL.Range("AK" & k).Value2 Then 'if MOS on list of ICTL MOSs then
                                shMaster.Range("K" & i).Value2 = shICTL.Range("AO" & k).Value2 'denominator
                                k = 31
                            Else
                                If k = 31 Then
                                    shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2
                                End If
                            End If
                        Next k
                        
                        For l = 2 To lRowT 'loop to count the numerator
                            If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'DODID check
                                found = 1
                                
                                Select Case Worksheets("DTMS Training").Range("R" & l).Value2
                                    Case "DA-CMT12" 'Resilience
                                        shMaster.Range("N" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT15" 'Personal Readiness
                                        shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT10" 'EO / EEO
                                        shMaster.Range("M" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT18" 'INFOSEC
                                        shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT16" 'OPSEC
                                        shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "150S-SHA-0100" 'SHARP
                                        shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT01" 'AT Lvl 1
                                        shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT19" 'Cyber Awareness
                                        shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "MC - 00020" 'HIPAA
                                        shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT02" 'TARP
                                        shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
                                End Select
                                
                                If InStr(shTraining.Range("W" & l).Value2, shMaster.Range("D" & i).Value2) = 1 Then
                                    ictlCount = ictlCount + 1
                                End If
                            Else
                                If found = 1 Then
                                    l = lRowT
                                    found = 0
                                Else
                                    If l = lRowT Then
                                        shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
                                    End If
                                End If
                            End If
                        Next l
                            If shMaster.Range("K" & i).Value2 = shMaster.Range("D" & i).Value2 Then
                                shMaster.Range("J" & i).Value2 = shMaster.Range("D" & i).Value2
                                shMaster.Range("L" & i).Value2 = shMaster.Range("D" & i).Value2
                            Else
                                shMaster.Range("J" & i).Value2 = ictlCount 'numerator
                                ictlCount = 0
                            End If
                    
                    Case 6, 16, 20
                        shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                        shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                        shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                        '79S and 91M are also exempt. add to code somehow to exempt those MOS? or any other MOS? that is excluded?
                        'i have the list.
                        'maybe assign execmpt MOS a case rankno like 61=79S 62=91M etc?
                    
                    'If Worksheets("Master Data").Range("C").Value = "79S" Then
                     '   Worksheets("Master Data").Range("J" & i).Value = "Exempt"
                    'End If
                    '66H has 3 spaces like this "66H   " for how it is from dtms
                    '68C has 2 spaces liek this "68C  " for how it is from dtms


                    Case 30
                        For l = 2 To lRowT 'start looking through the Training data
                            
                            If shMaster.Range("A" & i).Value2 = shTraining.Range("E" & l).Value2 Then 'If the DODID is found look at various cases
                                found = 1
                                Select Case shTraining.Range("R" & l).Value2
                                    Case "DA-CMT15" 'Personal Readiness
                                        shMaster.Range("O" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT18" 'INFOSEC
                                        shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT16" 'OPSEC
                                        shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "150S-SHA-0100" 'SHARP
                                        shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT01" 'AT Lvl 1
                                        shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT19" 'Cyber Awareness
                                        shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "MC - 00020" 'HIPAA
                                        shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT02" 'TARP
                                        shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
                                End Select
                                    shMaster.Range("F" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("G" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("H" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("I" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("M" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("N" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                            Else
                                If found = 1 Then
                                    l = l + lRowT
                                    found = 0
                                Else
                                    If l = lRowT Then
                                        shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
                                    End If
                                End If
                            End If
                        Next l
                    
                    Case 40 'CON. 6 Total Trainings Required
                        For l = 2 To lRowT 'start looking through the Training data
                            If UCase(shMaster.Range("B" & i).Value2) = shTraining.Range("A" & l).Value2 & ", " & shTraining.Range("C" & l).Value2 Then 'If the DODID is found look at various cases
                                found = 1
                                Select Case shTraining.Range("R" & l).Value2
                                    Case "DA-CMT18" 'INFOSEC
                                        shMaster.Range("P" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT16" 'OPSEC
                                        shMaster.Range("Q" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT01" 'AT Lvl 1
                                        shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT19" 'Cyber Awareness
                                        shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "MC - 00020" 'HIPAA
                                        shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT02" 'TARP
                                        shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
                                End Select
                                    shMaster.Range("F" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("G" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("H" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("I" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("M" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("N" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("R" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                            Else
                                If found = 1 Then
                                    l = l + lRowT
                                    found = 0
                                Else
                                    If l = lRowT Then
                                        shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
                                    End If
                                End If
                            End If
                        Next l
                    Case 50 'Local Nationals. 5 Total Trainings Required SHARP, AT Lvl 1, HIPAA, TARP
                        For l = 2 To lRowT 'start looking through the Training data
                            If UCase(shMaster.Range("B" & i).Value2) = shTraining.Range("A" & l).Value2 & ", " & shTraining.Range("C" & l).Value2 Then 'If the DODID is found look at various cases
                                found = 1
                                Select Case shTraining.Range("R" & l).Value2
                                    Case "150S-SHA-0100" 'SHARP
                                        shMaster.Range("R" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT01" 'AT Lvl 1
                                        shMaster.Range("S" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT19" 'Cyber Awareness
                                        shMaster.Range("T" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "MC - 00020" 'HIPAA
                                        shMaster.Range("U" & i).Value2 = shTraining.Range("P" & l).Value2
                                    Case "DA-CMT02" 'TARP
                                        shMaster.Range("V" & i).Value2 = shTraining.Range("P" & l).Value2
                                End Select
                                    shMaster.Range("F" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("G" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("H" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("I" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("J" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("K" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("L" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("M" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("N" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("O" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                                    shMaster.Range("P" & i).Value2 = shMaster.Range("C" & i).Value2 '"Exempt"
                            Else
                                If found = 1 Then
                                    l = l + lRowT
                                    found = 0
                                Else
                                    If l = lRowT Then
                                        shMaster.Range("J" & i).Value2 = "Not in DTMS Training"
                                    End If
                                End If
                            End If
                        Next l
                    Case Else
                        shMaster.Range("J" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("K" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("L" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("M" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("N" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("O" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("P" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("Q" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("R" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("S" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("T" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("U" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                        shMaster.Range("V" & i).Value2 = "TEST" 'shMaster.Range("C" & i).Value '"Exempt"
                End Select
    Next i


    With Columns("L:L")  'ICT % Complete Column
        .HorizontalAlignment = xlCenter
        .ColumnWidth = 9
        .NumberFormat = "0%"
         Range("L2").Select
           ActiveCell.FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-2]/RC[-1],"""")"
           Range("L2").Select
           Selection.AutoFill Destination:=Range("L2:L" & lRow)
        With .Font
            .Name = "arial"
            .Size = 10
            .Bold = True
        End With
    End With
    
    
    Worksheets("Master Data").Select
    shMaster.Range("A1").Select
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    'Dim DataObj As New MSForms.DataObject 'empty the clipboard
    'DataObj.SetText ""
    'DataObj.PutInClipboard
 
 End Sub

**Paul_Hossler** · 05-25-2022, 05:11 AM

Here's a basic demo example of using arrays to hold the 'lookup' values based on a 'key' one time

WS Master has Company as the key value, and uses that to lookup name, phone, etc. from WS Data

There are more elegant and sophisticated data structures, but arrays are pretty basic

**Paul_Hossler** · 05-25-2022, 05:37 AM

Here's a snippet of using the arrays to lookup 'Rank No' based on 'Rank' on 'DTMS Roster' sheet using 'Conversion Files'

Normally all the arrays would be loaded first and then just used

Personally I get confused with column numbers, so if it's a large project, I'll use Public Const to hold the column number; makes it more readable and if the WS gets adjusted, it's much easier to change

Public Const cfRank as Long = 3

etc.

Just some ideas to consider

Option Explicit


Dim aryRankNumber As Variant
Dim aryRankNumberKey As Variant


Dim wsRoster As Worksheet
Dim wsConversionFiles As Worksheet


Dim rRoster As Range


Sub Main()
    
    Call Init
        
    Call AddRankNumberFromRank


    Call Cleanup


End Sub




'----------------------------------------------------
Sub Init()
    Set wsRoster = Worksheets("DTMS Roster")
    Set rRoster = wsRoster.Cells(1, 1).CurrentRegion
    Set wsConversionFiles = Worksheets("Conversion Files")
    
    aryRankNumber = wsConversionFiles.Cells(1, 9).CurrentRegion
    aryRankNumberKey = Application.WorksheetFunction.Transpose(wsConversionFiles.Cells(1, 9).CurrentRegion.Columns(1))
    
    Application.ScreenUpdating = False
    
End Sub


Sub AddRankNumberFromRank()
    Dim iRow As Long, iMatch As Long
    
    With rRoster
        For iRow = 2 To .Rows.Count
    
            iMatch = 0
            On Error Resume Next
            iMatch = Application.WorksheetFunction.Match(.Cells(iRow, 3), aryRankNumberKey, 0)
            On Error GoTo 0
    
            If iMatch > 0 Then  '   found one
                .Cells(iRow, 7).Value = aryRankNumber(iMatch, 2)
            Else
                .Cells(iRow, 7).Value = "None"
            End If
        Next iRow
    End With


End Sub


Sub Cleanup()
    Application.ScreenUpdating = False
    
    MsgBox "Done"
End Sub

**Supersipe** · 05-25-2022, 05:51 AM

This is awesome. I have some more learning to do. Can you elaborate more on this:

"Normally all the arrays would be loaded first and then just used

Personally I get confused with column numbers, so if it's a large project, I'll use Public Const to hold the column number; makes it more readable and if the WS gets adjusted, it's much easier to change"

I am brand new to VBA. This is my first project in it! It just happened as the data kept coming and more questions were getting asked so i started to figure out a way that we could pull the data. Anyways, i'll take your suggestion, but by loaded it somewhere or preloading it? i was sort of tryig that with Conversion Files so i had a central sheet where i would do the vlookups or rankNo etc.

thanks for looking it over.

**Paul_Hossler** · 05-25-2022, 10:13 AM

Search the modules for !!! which I used as flag to answer your questions

I added another 'lookup' function to hopefully make the explanation clearer

Capture.JPG

**Supersipe** · 05-25-2022, 10:50 AM

you are awesome friend!

hopefully i can explain what i was thinking and the end result of what i want. Master Data needs to have the 5 columns (A:E). EPIDI, name (last, first), Rank, MOS, Main Unit. I was utilizing VLOOKUPS on the DTMS roster and then pasting the results of that as value2. i don't even need the RankNo or unit easy on DTMS roster, it was there as a reference point. Also, i don't/didn't know a better way to get name to be proper so i did the column with name for =proper(b2) and the VBA copies/converts it to a value then pastes that value over to the Main Unit.

so maybe if there is a way to utilize (like what you have shown earlier) doing the matching on DTMS Roster/Conversion Files and then pasting those results into the appropriate column on Master Data. The RankNo i believe is genius because it allows me to assign the trainings to a particular rank. However, that gives me some issues still because some ranks/mos are EXEMPT from ICT/Weapon.

I feel like i just went in the weeds a bit. Would it help if i laid out exactly how i want it to look like? For example: Rank 50 (local nationals) are exempt from ACFT/Weapons/ICT and a few of the BLUE trainings at the top. i want their respective rows to show "-" instead of "exempt".

actually, what would you recommend is placed in those cells where an individual is exempt"? maybe just leave blank? and if they are required to do it, and havn't it shows "No Data"??

**Paul_Hossler** · 05-25-2022, 11:29 AM

I'm more than a little confused

I just showed a (one of many) way to speedup the VLookup and then convert to values. I picked two simple pieces of your macro that used 'Conversion Files' data to 'translate' instead of Vlookup formulas

Now it looks like your overall objective is to create 'Master Data' columns A-V by joining data (using EPIDI as a key I assume) from DTMS Roster, Weapons, Training, ACFT, etc. and translate some fields using the conversion factors in 'Conversion Files'.

Correct????

**Supersipe** · 05-25-2022, 11:56 AM

EXACTLY!!

What I have done is a crazy snowball. I started out with one idea. (All formulas). And after a while, it was a mess. So someone suggested I just do it in VBA, and it could save me time and I share this with many other end users. So I started with the “raw data” which is the DTMS (system where I pull the raw data” and after building some things, discovered i need this feature, or discovered the overal process is SLOW and so now I’m at a point I feel more confused than ever. I have been reading and watching and learning all I can. But today, took 30 minutes to run!

I also believe I coild
get rid of all the vlookups because every sheet has the RankNo and clinic I believe that bogs it down. The DTMS Training has 23,000 cells that go from A-Z. So lots of data.

End result: I enter the raw data (DTMS sheets) and the MASTER Data pulls the data into it.

DTMS Roster
Feeds Master Data A-E

DTMS ACFT
Feeds Master Data F-G

DTMS Weapons
Feeds Master Data H-I

DTMS Training
Feeds Master Data J-Z

i use the EDIPI are get: ACFT
i use EDIPI, RANK TO GET: weapons, AWT
i use EDIPI, rank, MOS to get ICT, trainings

in My head it’s complicated, but I believe using your suggestion could make it easier. But overall, I may be over complicating it.

I just need it to find the EDIPI, find the training I want to track, and place the date the completed the training in the Master Data.

so maybe there is a super simple
way to make it all happen. Idk

**Paul_Hossler** · 05-25-2022, 05:26 PM

Been playing with making the Master (but I'm making guesses)

I use a hidden WS as a template with the column headings and formats. Macro copies this and it becomes the start of a new Master

I left your original Master in renamed as a check

I think I have Roster, ACFT, Weapons in there. They were fairly easy to map. Look at Conversion sheet

If I used a field directly it's green, didn't use at all is red, and if I used at input to a lookup or for some other intermediate purpose it's yellow

Capture.JPG

I need lot more clarification on where the data comes from, where it goes, and any rules for these:

1. DTMS Training -- Feeds Master Data J-Z
2. i use EDIPI, RANK TO GET: weapons, AWT
3. i use EDIPI, rank, MOS to get ICT, trainings
4. Seems to be some sheets that are not used and a lot of unneeded or not original columns on some sheets (might be leftover from your playing around)

A rule might be EDIPI + Rank+MOS on Master used to get matching ICT and trainings(???) from DTMS Training

**Supersipe** · 05-26-2022, 06:24 AM

thanks for the work! I'll break it all down. If i could use "rankNo" as a reference for the most part. or i'll just explain in general.

the EPIDI applies to all, EXCEPT Local Nationals (GER, GRN, BRN).

Master Data (Column A) EDIPI
Pulls data from DTMS Roster
End State:
Pulls data from DTMS Roster and pastes it as Value

Master Data (Column B) Last, First
Pulls data from DTMS Roster
End State:
1. The Last, First are in (Proper) format.
2. Pulls data from DTMS Roster and pastes it as Value.

Master Data (Column C) Rank
Pulls data from DTMS Roster
End State:
1. Pulls data from DTMS Roster and pastes it as Value

Master Data (Column E) Main Unit
Pulls data from DTMS Roster
End State:
1. Converts the Unit from the long name to the Unit Easy/Main Unit name (Vilseck) or just (HQ) not the massive long name.

ACFT Requirement:
Data is pulled from DTMS ACFT
<= 20 (This is PV1 - COL). So rankno 1-20.
*Note: the DTMS sheet has their info, date and score.
**End state:
1. Master Data ACFT Date pulls ACFT date from DTMS ACFT as well as ACFT Score of the individual.
2. If the individual is required to take the ACFT, but HAS NOT DATA, the column states "No Data" and ACFT score shows "0"
3. For everyone else it states, "Exempt"

Weapon Type/Date Requirement:
Data is pulled from DTMS Weapons
<=4 (rankno) the cell should state "M4"
>=5 (rankno) the cell should state "M17"
*note: this only needs to go to rank 20 because anyone above 20 are "Exempt"
**note: there are special people called "IMA" and they ARE NOT required to fire a weapon, even though they have a MIL rank. I have the list of those special individuals so if there is away i could fill those in later.
End state:
1. Master Data column (H) shows M4 for rankno <=4
2. Master Data column (H) shows M17 for rankno >=5 (but also <=20)
3. Cell states "IMA" for those special individuals.
4. Cell states "exempt" for everyone else
5. Master Data column (I) pulls the appropriate qualification date from DTMS Weapons. If required to shoot, and they HAVE NOT, cell shows "No Data"

ICT Complete/Assigned:
Data is pulled from DTMS Training (this is the main source where the rest of the cells information come from. it consists of over 22,000 cells (rows) and goes from columns A-Z
ICTs: ONLY the MOS's listed in the ICT Data Table are required to complete these.
*Note: the Assigned (denominator) is based off of the persons rank/mos.
**note: if the MOS IS NOT listed in the table, they are exempt, if they are a COL (16 rankno), they are exempt, if they are an IMA (those special case people, they are exempt.
***note: of course, everyone else is exempt (civ, con, ln)
End State:
1. If the individuals are required to complete the training it pulls the date from DTMS training.
2. If they are required to conduct a training, but have not, cell shows, "No Data"
3. If the individual is NOT required to complete the training, the cell shows, "Exempt"

ICT % Complete
this should just divide the assigned by complete and put the % there. Not sure what the cell should do if the adjacent cell states "exempt"? write exempt again?

Columns M-V (EO-TARP).
Data is pulled from DTMS Training
The requirements vary by "rank". for example: MIL are required EO, however, LN are not. I have the exact list of who is required and who is not.
End State:
1. If the individuals are required to complete the training it pulls the date from DTMS training.
2. If they are required to conduct a training, but have not, cell shows, "No Data"
3. If the individual is NOT required to complete the training, the cell shows, "Exempt"

AWT (there are 4, but i shared 2 as they are the same).
Data is pulled from DTMS Training
Rankno's: 1-4, 11-14 and 20 are required to complete AWT's (that is PVT - SFC, 2LT - MAJ, CW2)
End State:
1. If the individuals are required to complete the training it pulls the date from DTMS training.
2. If they are required to conduct a training, but have not, cell shows, "No Data"
3. If the individual is NOT required to complete the training, the cell shows, "Exempt"

General formatting:

[COLOR=var(--highlight-keyword)][/COLOR]
[COLOR=var(--highlight-keyword)]End[/COLOR]
[COLOR=var(--highlight-keyword)]With[/COLOR]
i would like column B to be in (proper). I found this formula today:

The formula however, when i try and run it, shows "Value" error

I think that is it. You have NO IDEA how thankful i am to be getting help with this.

Sub Macro_test_v1()
Dim lRow As Integer
With Worksheets("Master Data")
    lRow = .Cells(Rows.Count, "B").End(xlUp).Row
    .Range("B2:B" & lRow).Value2 = .Evaluate("INDEX(PROPER(B2:B & lRow & ")
End With
End Sub

**Aussiebear** · 05-26-2022, 06:56 AM

Originally Posted by Supersipe

test

What was that reply meant to indicate?

**Supersipe** · 05-26-2022, 06:59 AM

sorry, I was typing a long reply and thought it logged me out and I would lose my reply. So I quickly hit reply to ensure i could type the whole thing as I was able to break down the End state of each column in Master Data.

**Supersipe** · 05-26-2022, 07:01 AM

**Huge side Note**

I am stationed currently in Germany so the time difference is about 8 hours from most places in the states. I appreciate the views/feedback to get this project complete.

sorry, I was typing a long reply and thought it logged me out and I would lose my reply. So I quickly hit reply to ensure i could type the whole thing as I was able to break down the End state of each column in Master Data.

**Paul_Hossler** · 05-26-2022, 05:47 PM

Latest version - I think I'm good up to ICT, but I only have the exemptions there (MOS, COL, etc.)

I did consolidate the conversions since a lot of parameters were split in multiple places

Again, I had to make some guesses since the worksheets were sort of a mess, with a lot of duplicated data

Capture.JPG

I really need a better process map for the remaining of the training remaining

Also thinking on IMAs. If they have a EDIPI it's easist to make a list

**Supersipe** · 05-26-2022, 08:57 PM

Want me to send you a full workbook? and change the EPIDI/NAMES again?

Main Unit/Clinic shortcut are the same thing. So we only need 1.

next small issue: we have 8 or so individuals who where they unit is “weird”. I have those names as well. They have EDIPI’s.

when I get to work I’ll submit the sheet.

you have opened my eyes on how I should be looking at items. I think I sort of started with the helper sheet, but it needed more as I see you have added the weapons and exceptions to the table as well. Brilliant idea

**arnelgp** · 05-26-2022, 09:51 PM

imho, you should consider using Database rather than worksheet.
you might find band aid fix today. but in the the future as your data
gets bigger, it will eventually slows you down to impossible.

**Supersipe** · 05-27-2022, 02:32 AM

arnelgp,

can you expand on what you mean by database vs worksheet? like where it says: wsRoster=worksheets("DTMS Roster") ?? convert to database?

Attached is the FULL version. I removed all sensitive data and put simple names. On the conversion files, the Special Names, Those are the correct clinics i want them assigned to.

could anyone take a look at sheet DTMS Training, column W. It is referenced from ICTL Data Table. The problem i found the other day was the formulas in the VBA code were NOT reading after the first MOS.

ie: 60L, 60P, 61H, 61N is what is listed. but the ICTL counter (Master Data) is only counting 60L.

**i keep getting an error when trying to upload. i'll try to sign on again.

**Paul_Hossler** · 05-27-2022, 10:18 AM

Originally Posted by arnelgp

imho, you should consider using Database rather than worksheet.
you might find band aid fix today. but in the the future as your data
gets bigger, it will eventually slows you down to impossible.

MS Access might be better than Excel, but in my experience there are many, many more people that are familar with Excel than Access.

For a relatively simple structure like this (few tables, simple reporting requirements, uncomplicated processing, you can get by using Excel. Just my opinion

Thread: SLOW VBA (20+ minutes) with many loops and VLOOKUPS. Help please.

Thread Tools

Display

SLOW VBA (20+ minutes) with many loops and VLOOKUPS. Help please.

Posting Permissions