Sub ListkinhNghiem()
Dim aHoTen(), aLyLich(), aDanhSach(), S, S2, Res(), ikey$, tmp$
Dim eRow&, sRow&, i&, j&, iR&, jC&, k&
With Sheets("List_Kinh Nghiem")
eRow = .Range("D" & Rows.Count).End(xlUp).Row
If eRow < 7 Then MsgBox ("Khong co du lieu"): Exit Sub
aDanhSach = .Range("D7:D" & eRow).Value
End With
With Sheets("DM_HD")
eRow = .Range("D" & Rows.Count).End(xlUp).Row 'Cot Ten nguoi lao dong
If eRow < 15 Then MsgBox ("Khong co du lieu"): Exit Sub
aHoTen = .Range("D15:D" & eRow).Value
aLyLich = .Range("AA15:AA" & eRow).Value
End With
sRow = UBound(aDanhSach, 1)
k = 1
ReDim Res(1 To UBound(aDanhSach, 1), 1 To 3 * k)
With CreateObject("scripting.dictionary")
For i = 1 To sRow
ikey = aDanhSach(i, 1)
If Len(ikey) > 0 Then .Item(ikey) = i
Next i
sRow = UBound(aHoTen, 1)
For i = 1 To sRow
iR = .Item(aHoTen(i, 1))
If iR > 0 Then
S = Split(Chr(10) & aLyLich(i, 1), Chr(10))
For j = 1 To UBound(S)
tmp = Mid(S(j), 3, Len(S(j)))
If InStr(1, tmp, "/") > 0 Then
S2 = Split(tmp, "/")
If j > k Then
k = j
ReDim Preserve Res(1 To UBound(aDanhSach, 1), 1 To 3 * k)
End If
jC = 3 * (j - 1) + 1
Res(iR, jC) = S2(0)
Res(iR, jC + 1) = S2(1)
If UBound(S2) = 2 Then Res(iR, jC + 2) = S2(2)
End If
Next j
End If
Next i
End With
With Sheets("List_Kinh Nghiem")
.Range("E7").Resize(UBound(Res), UBound(Res, 2)) = Res
End With
End Sub