Một cách khác quản lý nhiều công thức: xem hình để biết sử dụng công thức nhé.
Function BaoHiem(sourceHT As Range, sourceBH As Range, target As Range, hoten As Range) As String
If sourceHT.Row <> sourceBH.Row Or sourceHT.Rows.Count <> sourceBH.Rows.Count Then
BaoHiem = "Sai vung tham chieu"
Exit Function
End If
'Tao Dic 1
iarr = Split(Trim(target.Text))
Set iDic = CreateObject("Scripting.Dictionary")
For i = LBound(iarr) To UBound(iarr)
If Trim(iarr(i)) <> "" And Not iDic.exists(iarr(i)) Then
j = j + 1
iDic.Add iarr(i), j
End If
Next
'Tao Dic 2
SRow = sourceBH.Row
ERow = sourceBH.Rows.Count + SRow - 1
SCol = sourceBH.Column
ECol = sourceBH.Columns.Count + SCol - 1
Set sDic = CreateObject("Scripting.Dictionary")
For i = SRow To ERow
If Cells(i, sourceHT.Column).Value = hoten.Text Then
For j = SCol To ECol
If Trim(Cells(i, j).Text) <> "" And Not sDic.exists(Cells(i, j).Text) Then
k = k + 1
sDic.Add Cells(i, j).Text, k
End If
Next
End If
Next
'So sanh thuan
For Each key1 In iDic.keys
If Not sDic.exists(key1) Then
BaoHiem = BaoHiem & key1 & " "
End If
Next
'So sanh nghich
For Each key2 In sDic.keys
If Not iDic.exists(key2) Then
BaoHiem = BaoHiem & key2 & " "
End If
Next
BaoHiem = Trim(BaoHiem)
End Function
