Sub test()
Dim sArr1(), sArr2(), Arr() As Variant
Dim i, j, n As Integer
Dim tmp As String
sArr1 = Sheets("Dulieu").Range("B4:G9").Value
sArr2 = Sheets("Dulieu").Range("L4:V18").Value
ReDim Arr(1 To UBound(sArr1, 1) + UBound(sArr2, 1), 1 To 6)
For i = 1 To UBound(sArr1, 1)
n = n + 1
Arr(n, 1) = sArr1(i, 2)
If sArr1(i, 3) <> "" Then
Arr(n, 1) = Arr(n, 1) & ", Sinh nam:" & sArr1(i, 3)
End If
If sArr1(i, 4) <> "" Then
Arr(n, 1) = Arr(n, 1) & ", CMND:" & sArr1(i, 4)
End If
For j = 1 To UBound(sArr2, 1)
If sArr1(i, 1) = sArr2(j, 1) Then
n = n + 1
If sArr2(j, 4) <> "" Then
Arr(n, 1) = sArr2(j, 3) & ", " & sArr2(j, 4) & "m"
Else: Arr(n, 1) = sArr2(j, 3)
End If
Arr(n, 2) = sArr2(j, 5)
Arr(n, 3) = sArr2(j, 6)
Arr(n, 4) = sArr2(j, 7)
Arr(n, 5) = sArr2(j, 8)
Arr(n, 6) = sArr2(j, 9)
End If
Next
Next
Sheets("ThamDinh").Range("B20").Resize(n, 6) = Arr
End Sub