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

[Supersipe]

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]

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]

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]

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]

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

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

29793

[Supersipe]

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]

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]

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]

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

29795



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]

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:



End
With
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]

testWhat was that reply meant to indicate?

[Supersipe]

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]

**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]

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

29799

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]

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]

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]

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]

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