Xin giúp code lọc dữ liệu với điều kiện (2 người xem)

Elx Liên hệ QC

Người dùng đang xem chủ đề này

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
P

phuoclocvl

Thành viên thường trực
Tham gia
28/3/12
Bài viết
220
Được thích
32
Chào các A/C GPE,
xin giúp em code lọc dữ liệu như bên dưới em có dữ liệu như bên dưới
hoặc xem file đính kèm giúp em
Cảm ơn.

DỮ LIỆU GỐC:

[TABLE="class: grid, width: 923"]
[TR]
[TD] Item [/TD]
[TD] Description [/TD]
[TD] Whitebody [/TD]
[TD] Mod [/TD]
[TD] Main
Machine [/TD]
[TD]23-Oct[/TD]
[TD]24-Oct[/TD]
[TD]25-Oct[/TD]
[TD]26-Oct[/TD]
[TD]27-Oct[/TD]
[TD]28-Oct[/TD]
[TD]29-Oct[/TD]
[/TR]
[TR]
[TD]01131[/TD]
[TD]PLASTIC MATERIAL 01131[/TD]
[TD]01131-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]14000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]01350[/TD]
[TD]PLASTIC MATERIAL FOR 01350[/TD]
[TD]01350-PLST[/TD]
[TD]01350MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]01469[/TD]
[TD]PLST MATERIAL FOR 01469 01468[/TD]
[TD]01468-PLST[/TD]
[TD]01468MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]810[/TD]
[/TR]
[TR]
[TD]01750[/TD]
[TD]BALL TOP PLASTIC MATERIAL (T)[/TD]
[TD]01750WHT-PLTS/T[/TD]
[TD]01750TOP-MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]01772[/TD]
[TD]B182-92 MDF RAIL[/TD]
[TD]01772-PLST[/TD]
[TD]01772MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]01773[/TD]
[TD]B182-46 MDF RAIL[/TD]
[TD]01773-PLST[/TD]
[TD]01773MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]02916[/TD]
[TD]PLASTIC FLUTED BUNFOOT W/BOLT[/TD]
[TD]02912-PLST[/TD]
[TD]04058MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]02964[/TD]
[TD]B21X PLASTIC BUNFOOT MATERIAL[/TD]
[TD]02964-PLST[/TD]
[TD]02139MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]03098[/TD]
[TD]B136 PLASTIC KNOB PLASTIC[/TD]
[TD]03098-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]11400[/TD]
[/TR]
[/TABLE]


SAU KHI LỌC
[TABLE="class: grid, width: 711"]
[TR]
[TD] Item [/TD]
[TD] Description [/TD]
[TD] Whitebody [/TD]
[TD] Mod [/TD]
[TD] Main
Machine [/TD]
[TD] Quantity [/TD]
[TD] Date [/TD]
[/TR]
[TR]
[TD]01131[/TD]
[TD]PLASTIC MATERIAL 01131[/TD]
[TD]01131-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]14000[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]01350[/TD]
[TD]PLASTIC MATERIAL FOR 01350[/TD]
[TD]01350-PLST[/TD]
[TD]01350MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[TR]
[TD]01469[/TD]
[TD]PLST MATERIAL FOR 01469 01468[/TD]
[TD]01468-PLST[/TD]
[TD]01468MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]810[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[TR]
[TD]01750[/TD]
[TD]BALL TOP PLASTIC MATERIAL (T)[/TD]
[TD]01750WHT-PLTS/T[/TD]
[TD]01750TOP-MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]24-Oct[/TD]
[/TR]
[TR]
[TD]01772[/TD]
[TD]B182-92 MDF RAIL[/TD]
[TD]01772-PLST[/TD]
[TD]01772MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]01773[/TD]
[TD]B182-46 MDF RAIL[/TD]
[TD]01773-PLST[/TD]
[TD]01773MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]02916[/TD]
[TD]PLASTIC FLUTED BUNFOOT W/BOLT[/TD]
[TD]02912-PLST[/TD]
[TD]04058MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[TR]
[TD]02964[/TD]
[TD]B21X PLASTIC BUNFOOT MATERIAL[/TD]
[TD]02964-PLST[/TD]
[TD]02139MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]27-Oct[/TD]
[/TR]
[TR]
[TD]02964[/TD]
[TD]B21X PLASTIC BUNFOOT MATERIAL[/TD]
[TD]02964-PLST[/TD]
[TD]02139MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]28-Oct[/TD]
[/TR]
[TR]
[TD]03098[/TD]
[TD]B136 PLASTIC KNOB PLASTIC[/TD]
[TD]03098-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]28-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]24-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]25-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]26-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]27-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]28-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]11400[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[/TABLE]
 

File đính kèm

Sắp xếp theo thời gian sắp xếp theo bầu chọn
phuoclocvl đã viết:
Chào các A/C GPE,
xin giúp em code lọc dữ liệu như bên dưới em có dữ liệu như bên dưới
hoặc xem file đính kèm giúp em
Cảm ơn.

DỮ LIỆU GỐC:

[TABLE="class: grid, width: 923"]
[TR]
[TD] Item[/TD]
[TD] Description[/TD]
[TD] Whitebody[/TD]
[TD] Mod[/TD]
[TD] Main
Machine[/TD]
[TD]23-Oct[/TD]
[TD]24-Oct[/TD]
[TD]25-Oct[/TD]
[TD]26-Oct[/TD]
[TD]27-Oct[/TD]
[TD]28-Oct[/TD]
[TD]29-Oct[/TD]
[/TR]
[TR]
[TD]01131[/TD]
[TD]PLASTIC MATERIAL 01131[/TD]
[TD]01131-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]14000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]01350[/TD]
[TD]PLASTIC MATERIAL FOR 01350[/TD]
[TD]01350-PLST[/TD]
[TD]01350MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]01469[/TD]
[TD]PLST MATERIAL FOR 01469 01468[/TD]
[TD]01468-PLST[/TD]
[TD]01468MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]810[/TD]
[/TR]
[TR]
[TD]01750[/TD]
[TD]BALL TOP PLASTIC MATERIAL (T)[/TD]
[TD]01750WHT-PLTS/T[/TD]
[TD]01750TOP-MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]01772[/TD]
[TD]B182-92 MDF RAIL[/TD]
[TD]01772-PLST[/TD]
[TD]01772MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]01773[/TD]
[TD]B182-46 MDF RAIL[/TD]
[TD]01773-PLST[/TD]
[TD]01773MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]02916[/TD]
[TD]PLASTIC FLUTED BUNFOOT W/BOLT[/TD]
[TD]02912-PLST[/TD]
[TD]04058MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]02964[/TD]
[TD]B21X PLASTIC BUNFOOT MATERIAL[/TD]
[TD]02964-PLST[/TD]
[TD]02139MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]03098[/TD]
[TD]B136 PLASTIC KNOB PLASTIC[/TD]
[TD]03098-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]11400[/TD]
[/TR]
[/TABLE]


SAU KHI LỌC
[TABLE="class: grid, width: 711"]
[TR]
[TD] Item[/TD]
[TD] Description[/TD]
[TD] Whitebody[/TD]
[TD] Mod[/TD]
[TD] Main
Machine[/TD]
[TD] Quantity[/TD]
[TD] Date[/TD]
[/TR]
[TR]
[TD]01131[/TD]
[TD]PLASTIC MATERIAL 01131[/TD]
[TD]01131-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]14000[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]01350[/TD]
[TD]PLASTIC MATERIAL FOR 01350[/TD]
[TD]01350-PLST[/TD]
[TD]01350MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[TR]
[TD]01469[/TD]
[TD]PLST MATERIAL FOR 01469 01468[/TD]
[TD]01468-PLST[/TD]
[TD]01468MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]810[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[TR]
[TD]01750[/TD]
[TD]BALL TOP PLASTIC MATERIAL (T)[/TD]
[TD]01750WHT-PLTS/T[/TD]
[TD]01750TOP-MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]24-Oct[/TD]
[/TR]
[TR]
[TD]01772[/TD]
[TD]B182-92 MDF RAIL[/TD]
[TD]01772-PLST[/TD]
[TD]01772MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]01773[/TD]
[TD]B182-46 MDF RAIL[/TD]
[TD]01773-PLST[/TD]
[TD]01773MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]02916[/TD]
[TD]PLASTIC FLUTED BUNFOOT W/BOLT[/TD]
[TD]02912-PLST[/TD]
[TD]04058MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[TR]
[TD]02964[/TD]
[TD]B21X PLASTIC BUNFOOT MATERIAL[/TD]
[TD]02964-PLST[/TD]
[TD]02139MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]27-Oct[/TD]
[/TR]
[TR]
[TD]02964[/TD]
[TD]B21X PLASTIC BUNFOOT MATERIAL[/TD]
[TD]02964-PLST[/TD]
[TD]02139MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]28-Oct[/TD]
[/TR]
[TR]
[TD]03098[/TD]
[TD]B136 PLASTIC KNOB PLASTIC[/TD]
[TD]03098-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]28-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]24-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]25-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]26-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]27-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]28-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]11400[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[/TABLE]
Nhấp chuột vào đây để mở rộng...
Thử code này xem:
Mã: 
Public Sub Loc()
    Dim Vung, VungDem, I, J, K, kK, Mg, Ngay, Wf, iDem
        Vung = Sheets("Data").Range(Sheets("Data").[A3], Sheets("Data").[A100000].End(xlUp)).Resize(, 12)
        Set VungDem = Sheets("Data").Range("F3").Resize(UBound(Vung), 7)
        Set Wf = Application.WorksheetFunction
        Ngay = Range("F2:L2")
        iDem = Wf.CountIf(VungDem, ">0")
        ReDim Mg(1 To iDem, 1 To UBound(Vung, 2) - 5)
            For I = 1 To UBound(Vung)
                If Wf.CountIf(VungDem.Rows(I), ">0") Then
                    For J = 1 To VungDem.Columns.Count
                        If VungDem(I, J) > 0 Then
                            K = K + 1
                            Mg(K, 1) = Vung(I, 1): Mg(K, 2) = Vung(I, 2): Mg(K, 3) = Vung(I, 3): Mg(K, 4) = Vung(I, 4): Mg(K, 5) = Vung(I, 5)
                            Mg(K, 6) = VungDem(I, J): Mg(K, 7) = Ngay(1, J)
                        End If
                    Next J
                End If
            Next I
    Sheets("Get").[I3:O10000].ClearContents
    Sheets("Get").[I3].Resize(iDem, UBound(Vung, 2) - 5) = Mg
End Sub
Kết quả ở cột của sheet "Get"
 

File đính kèm

Upvote 0
phuoclocvl đã viết:
Chào các A/C GPE,
xin giúp em code lọc dữ liệu như bên dưới em có dữ liệu như bên dưới
hoặc xem file đính kèm giúp em
Cảm ơn.

DỮ LIỆU GỐC:

[TABLE="class: grid, width: 923"]
[TR]
[TD] Item[/TD]
[TD] Description[/TD]
[TD] Whitebody[/TD]
[TD] Mod[/TD]
[TD] Main
Machine[/TD]
[TD]23-Oct[/TD]
[TD]24-Oct[/TD]
[TD]25-Oct[/TD]
[TD]26-Oct[/TD]
[TD]27-Oct[/TD]
[TD]28-Oct[/TD]
[TD]29-Oct[/TD]
[/TR]
[TR]
[TD]01131[/TD]
[TD]PLASTIC MATERIAL 01131[/TD]
[TD]01131-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]14000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]01350[/TD]
[TD]PLASTIC MATERIAL FOR 01350[/TD]
[TD]01350-PLST[/TD]
[TD]01350MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD]01469[/TD]
[TD]PLST MATERIAL FOR 01469 01468[/TD]
[TD]01468-PLST[/TD]
[TD]01468MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]810[/TD]
[/TR]
[TR]
[TD]01750[/TD]
[TD]BALL TOP PLASTIC MATERIAL (T)[/TD]
[TD]01750WHT-PLTS/T[/TD]
[TD]01750TOP-MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]01772[/TD]
[TD]B182-92 MDF RAIL[/TD]
[TD]01772-PLST[/TD]
[TD]01772MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]01773[/TD]
[TD]B182-46 MDF RAIL[/TD]
[TD]01773-PLST[/TD]
[TD]01773MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]02916[/TD]
[TD]PLASTIC FLUTED BUNFOOT W/BOLT[/TD]
[TD]02912-PLST[/TD]
[TD]04058MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]02964[/TD]
[TD]B21X PLASTIC BUNFOOT MATERIAL[/TD]
[TD]02964-PLST[/TD]
[TD]02139MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]03098[/TD]
[TD]B136 PLASTIC KNOB PLASTIC[/TD]
[TD]03098-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]11400[/TD]
[/TR]
[/TABLE]


SAU KHI LỌC
[TABLE="class: grid, width: 711"]
[TR]
[TD] Item[/TD]
[TD] Description[/TD]
[TD] Whitebody[/TD]
[TD] Mod[/TD]
[TD] Main
Machine[/TD]
[TD] Quantity[/TD]
[TD] Date[/TD]
[/TR]
[TR]
[TD]01131[/TD]
[TD]PLASTIC MATERIAL 01131[/TD]
[TD]01131-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]14000[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]01350[/TD]
[TD]PLASTIC MATERIAL FOR 01350[/TD]
[TD]01350-PLST[/TD]
[TD]01350MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[TR]
[TD]01469[/TD]
[TD]PLST MATERIAL FOR 01469 01468[/TD]
[TD]01468-PLST[/TD]
[TD]01468MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]810[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[TR]
[TD]01750[/TD]
[TD]BALL TOP PLASTIC MATERIAL (T)[/TD]
[TD]01750WHT-PLTS/T[/TD]
[TD]01750TOP-MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]24-Oct[/TD]
[/TR]
[TR]
[TD]01772[/TD]
[TD]B182-92 MDF RAIL[/TD]
[TD]01772-PLST[/TD]
[TD]01772MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]01773[/TD]
[TD]B182-46 MDF RAIL[/TD]
[TD]01773-PLST[/TD]
[TD]01773MOD[/TD]
[TD]408T[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]02916[/TD]
[TD]PLASTIC FLUTED BUNFOOT W/BOLT[/TD]
[TD]02912-PLST[/TD]
[TD]04058MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[TR]
[TD]02964[/TD]
[TD]B21X PLASTIC BUNFOOT MATERIAL[/TD]
[TD]02964-PLST[/TD]
[TD]02139MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]1700[/TD]
[TD="align: right"]27-Oct[/TD]
[/TR]
[TR]
[TD]02964[/TD]
[TD]B21X PLASTIC BUNFOOT MATERIAL[/TD]
[TD]02964-PLST[/TD]
[TD]02139MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]28-Oct[/TD]
[/TR]
[TR]
[TD]03098[/TD]
[TD]B136 PLASTIC KNOB PLASTIC[/TD]
[TD]03098-PLST[/TD]
[TD]01131MOD[/TD]
[TD]178T[/TD]
[TD="align: right"]6000[/TD]
[TD="align: right"]28-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]23-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]24-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]25-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]26-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]27-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]13800[/TD]
[TD="align: right"]28-Oct[/TD]
[/TR]
[TR]
[TD]04066[/TD]
[TD]76MM DWR GLIDE SPACER PLST[/TD]
[TD]04066-PLST[/TD]
[TD]04066MOD[/TD]
[TD]268T[/TD]
[TD="align: right"]11400[/TD]
[TD="align: right"]29-Oct[/TD]
[/TR]
[/TABLE]
Nhấp chuột vào đây để mở rộng...

Thêm 1 cách để bạn tham khảo:

Mã: 
Sub Loc()    
    Dim SrcArr, ResArr()
    Dim lR As Long, lC As Long, k As Long
    
    SrcArr = Sheet1.Range(Sheet1.Range("A2"), Sheet1.Range("A65000").End(xlUp)).Resize(, 12).Value2
    ReDim ResArr(1 To UBound(SrcArr, 1) * 7, 1 To 7)
    
        For lR = 2 To UBound(SrcArr, 1)
            For lC = 6 To 12
                If SrcArr(lR, lC) > 0 Then
                    k = k + 1
                    ResArr(k, 1) = CStr(SrcArr(lR, 1))
                    ResArr(k, 2) = CStr(SrcArr(lR, 2))
                    ResArr(k, 3) = CStr(SrcArr(lR, 3))
                    ResArr(k, 4) = CStr(SrcArr(lR, 4))
                    ResArr(k, 5) = CStr(SrcArr(lR, 5))
                    ResArr(k, 6) = CLng(SrcArr(lR, lC))
                    ResArr(k, 7) = SrcArr(1, lC)
                End If
            Next lC
        Next lR
    If k Then
        With Sheet2
            .Range("A3:G10000").ClearContents
            .Range("A3").Resize(k, 7).Value = ResArr
        End With
    End If
End Sub
 
Lần chỉnh sửa cuối:
Upvote 0
mhung12005 đã viết:
Thêm 1 cách để bạn tham khảo:

Mã: 
Sub Loc()    
    Dim SrcArr, ResArr()
    Dim lR As Long, lC As Long, k As Long
    
    SrcArr = Sheet1.Range(Sheet1.Range("A2"), Sheet1.Range("A65000").End(xlUp)).Resize(, 12).Value2
    ReDim ResArr(1 To UBound(SrcArr, 1) * 7, 1 To 7)
    
        For lR = 2 To UBound(SrcArr, 1)
            For lC = 6 To 12
                If SrcArr(lR, lC) > 0 Then
                    k = k + 1
                    ResArr(k, 1) = CStr(SrcArr(lR, 1))
                    ResArr(k, 2) = CStr(SrcArr(lR, 2))
                    ResArr(k, 3) = CStr(SrcArr(lR, 3))
                    ResArr(k, 4) = CStr(SrcArr(lR, 4))
                    ResArr(k, 5) = CStr(SrcArr(lR, 5))
                    ResArr(k, 6) = CLng(SrcArr(lR, lC))
                    ResArr(k, 7) = SrcArr(1, lC)
                End If
            Next lC
        Next lR
    If k Then
        With Sheet2
            .Range("A3:G10000").ClearContents
            .Range("A3").Resize(k, 7).Value = ResArr
        End With
    End If
End Sub
Nhấp chuột vào đây để mở rộng...

Cảm ơn bạn, code chuẩn
 
Upvote 0
Mình cũng đã viết được rồi, nhưng code có vẻ lủn củng lắm, các bạn chỉnh giúp mình nha
Cảm ơn

Public Sub LocData()
Dim i, j, k As Long
Dim sht As Worksheet
Dim sht1 As Worksheet
Set sht = Worksheets("Data")
Set sht1 = Worksheets("Get")
k = 1
sht1.Range("A2:G65000").ClearContents
With sht1
.Cells(1, 1) = "ITEM"
.Cells(1, 2) = "DESCRIPTION"
.Cells(1, 3) = "WHITE BODY"
.Cells(1, 4) = "MOD"
.Cells(1, 5) = "MACHINE"
.Cells(1, 6) = "QTY"
.Cells(1, 7) = "DATE"
End With

For i = 2 To sht.UsedRange.Rows.Count
For j = 6 To 12
If sht.Cells(i, j).Value > 0 Then
k = k + 1
sht1.Cells(k, 1) = sht.Cells(i, 1)
sht1.Cells(k, 2) = sht.Cells(i, 2)
sht1.Cells(k, 3) = sht.Cells(i, 3)
sht1.Cells(k, 4) = sht.Cells(i, 4)
sht1.Cells(k, 5) = sht.Cells(i, 5)
sht1.Cells(k, 6) = sht.Cells(i, j)
sht1.Cells(k, 7) = sht.Cells(1, j)
End If
Next j
Next i

End Sub
 
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.

Bài viết mới nhất

L
Tính thời gian làm việc thực tế
Trả lời
66
Đọc
14K
YenTon
Y
H
Chia sẻ file in Phiếu điều tra PCGD
    • Thích
    • Yêu thích
    • Cảm ơn
Trả lời
13
Đọc
631
Phuong7686
P
GPE-Trợ Lý
  • Dán lên cao
[KHẨN] HƯỚNG VỀ ĐỒNG BÀO CHỊU ẢNH HƯỞNG CỦA BÃO YAGI - BẢO YÊN, LÀO CAI (2 người xem)
    • Thích
    • Cảm ơn
    • Yêu thích
Trả lời
91
Đọc
14K
SA_DQ
SA_DQ
SA_DQ
Copilot bình luận về một ván cờ vua
    • Thích
Trả lời
6
Đọc
409
SA_DQ
SA_DQ
92nguyenthanh
  • Question Question
Vba cho file nhập xuất kho
Trả lời
4
Đọc
110
HUONGHCKT
H
C
Fix lỗi Application.InputBox Type:=8
    • Cười
Trả lời
25
Đọc
618
ptm0412
ptm0412
GPE-Trợ Lý
[GPE] TALK SHOW 08: GIẢI QUYẾT VẤN ĐỀ TỐI ƯU HÓA TRONG EXCEL VỚI CÔNG CỤ SOLVER (2 người xem)
    • Thích
    • Cảm ơn
Trả lời
4
Đọc
3K
DuongLeHung
D
Gà Công Nghệ
Chế độ chính sách cho giáo viên mầm non giữ trẻ khuyết tật
Trả lời
1
Đọc
58
vanle33
vanle33
huuthang_bd
Add-in DeleteVirusMacro - Xóa và ngăn chặn virus macro
    • Thích
    • Yêu thích
    • Cảm ơn
Trả lời
56
Đọc
22K
dunghtgiga
dunghtgiga
L
  • Question Question
Viết code tìm kiếm các file trong Folder
Trả lời
40
Đọc
12K
lan nguyen0987
L
P
Hyperlink_Cách chuyển cả thư mục và file tổng hợp sang máy khác không bị lỗi
Trả lời
5
Đọc
109
phongvanvu
P
HeSanbi
Tiện ích Web: Tự động nhấn nút "Bỏ qua" quảng cáo Youtube
    • Thích
    • Yêu thích
Trả lời
14
Đọc
1K
ongke0711
ongke0711
ptm0412
Quá tin tưởng vào AI các thể loại
    • Cười
Trả lời
4
Đọc
219
Maika8008
Maika8008
A
  • Question Question
Không tìm được lỗi code
Trả lời
6
Đọc
160
ptm0412
ptm0412
W
Tham chiếu theo chuỗi ký tự
Trả lời
8
Đọc
315
dungpham01
D
tritv.dad
Lấy dữ liệu từ folder các tờ khai thông quan để theo dõi hiệu suất công việc.
Trả lời
8
Đọc
287
BlueSphere
B
GPE-Trợ Lý
MỪNG NGÀY NHÀ GIÁO VIỆT NAM 20/11 (2 người xem)
    • Thích
Trả lời
0
Đọc
3K
GPE-Trợ Lý
GPE-Trợ Lý
SA_DQ
Xin chúc mừng các thầy cô giáo trên cộng đồng nhân 20/11
    • Thích
    • Yêu thích
Trả lời
1
Đọc
136
BuiQuangThuan
B
M
chuyển đổi số thành chữ
Trả lời
15
Đọc
371
dungpham01
D
V
Lỗi copy sheet: the name already exists.
Trả lời
7
Đọc
702
nghiabros
N
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Thời gian đếm ngược.

000
Ngày
00
Giờ
00
phút
00
giây
Thân mời tham dự sự kiện sinh nhật GPE 2025
Back
Top Bottom