Lọc số phiếu duy nhất và tìm số phiếu lớn nhất

4 Responses

1. hands says:

   26/05/2023 at 1:17 pm

   Bạn thử công thức xem có đúng ko

   C2=INDEX(MID($A$2:$A$19,1,SEARCH("_",$A$2:$A$19)-1),AGGREGATE(15,6,ROW($1:$20)/(MATCH(MID($A$2:$A$19,1,SEARCH("_",$A$2:$A$19)-1),MID($A$2:$A$19,1,SEARCH("_",$A$2:$A$19)-1),0)=ROW($1:$18)),ROW(A1)))

   D2=AGGREGATE(14,6,–MID($A$2:$A$19,SEARCH("_",$A$2:$A$19)+1,10)/(MID($A$2:$A$19,1,SEARCH("_",$A$2:$A$19)-1)=C2),1)

   Bạn xem trong file nhé

   Bạn có thể tham khảo nhé!
   6818

   Reply
2. hands says:

   26/05/2023 at 1:17 pm

   Bạn có thể tham khảo nhé!
   6818

   Công thức cho C2:

   =IFERROR(TRIM(LEFT(SUBSTITUTE(INDEX($A$2:$A$19,AGGREGATE(15,6,ROW($A$1:$A$19)/(COUNTIF($C$1:C1,LEFT($A$2:$A$19,FIND("_",$A$2:$A$19)-1))=0),ROW(A1))),"_",REPT(" ",100)),100)),"")

   Công thức cho D2:

   =AGGREGATE(14,6,--SUBSTITUTE($A$2:$A$19,C2&"_",""),1)

   .

   Reply
3. hands says:

   26/05/2023 at 1:17 pm

   Công thức cho C2:

   =IFERROR(TRIM(LEFT(SUBSTITUTE(INDEX($A$2:$A$19,AGGREGATE(15,6,ROW($A$1:$A$19)/(COUNTIF($C$1:C1,LEFT($A$2:$A$19,FIND("_",$A$2:$A$19)-1))=0),ROW(A1))),"_",REPT(" ",100)),100)),"")

   Công thức cho D2:

   =AGGREGATE(14,6,--SUBSTITUTE($A$2:$A$19,C2&"_",""),1)

   Thử tham khảo cách dùng code vba xem nhé

   Option Explicit
   Sub GPE()
       Dim dic As Object, key, Lr&, i&, Arr(), t
       Set dic = CreateObject("Scripting.Dictionary")
       With Sheets("Sheet1")
           Lr = .Range("A" & Rows.Count).End(xlUp).Row
           Arr = .Range("A2: A" & Lr).Value
           For i = 1 To UBound(Arr)
               key = Split(Arr(i, 1), "_")(0)
               If Not dic.exists(key) Then
                   dic.Add (key), Split(Arr(i, 1), "_")(1) * 1
               Else
                   If Split(Arr(i, 1), "_")(1) * 1 > dic.Item(key) Then
                       dic.Item(key) = Split(Arr(i, 1), "_")(1) * 1
                   End If
               End If
           Next i
           .Range("F2:G1000").ClearContents
           t = dic.Count
           With Application
               .Range("F2").Resize(t, 1).Value = .Transpose(dic.keys)
               .Range("G2").Resize(t, 1).Value = .Transpose(dic.items)
           End With
       End With
       MsgBox "Done"
       Set dic = Nothing
   End Sub

   Reply
4. hands says:

   26/05/2023 at 1:18 pm

   Thử tham khảo cách dùng code vba xem nhé

   Option Explicit
   Sub GPE()
       Dim dic As Object, key, Lr&, i&, Arr(), t
       Set dic = CreateObject("Scripting.Dictionary")
       With Sheets("Sheet1")
           Lr = .Range("A" & Rows.Count).End(xlUp).Row
           Arr = .Range("A2: A" & Lr).Value
           For i = 1 To UBound(Arr)
               key = Split(Arr(i, 1), "_")(0)
               If Not dic.exists(key) Then
                   dic.Add (key), Split(Arr(i, 1), "_")(1) * 1
               Else
                   If Split(Arr(i, 1), "_")(1) * 1 > dic.Item(key) Then
                       dic.Item(key) = Split(Arr(i, 1), "_")(1) * 1
                   End If
               End If
           Next i
           .Range("F2:G1000").ClearContents
           t = dic.Count
           With Application
               .Range("F2").Resize(t, 1).Value = .Transpose(dic.keys)
               .Range("G2").Resize(t, 1).Value = .Transpose(dic.items)
           End With
       End With
       MsgBox "Done"
       Set dic = Nothing
   End Sub

   Thử nhé bạn:
   Lọc duy nhất:

   H2=IF(H1="",LEFT(A2,FIND("_",A2)-1),INDEX(LEFT($A$2:$A$19,FIND("_",$A$2:$A$19)-1),MATCH(0,COUNTIF(H1:H$2,LEFT($A$2:$A$19,FIND("_",$A$2:$A$19)-1)),0),))

   Tìm số phiếu lớn nhất:

   I2=MAX(MID($A$2:$A$19,FIND("_",$A$2:$A$19)+1,5)*COUNTIF(H2,LEFT($A$2:$A$19,FIND("_",$A$2:$A$19)-1)))

   Thử nhé bạn:

   Reply