Lọc SĐT theo điều kiện & loại bỏ dữ liệu trùng lắp từ cột (1 người xem)

[htlamvdc]

Xin chào các cao thủ về Excell mình cũng ít khi dùng Excel và đang gặp phải vấn đề lọc data mong mọi người giúp với.- Mình có một bảng Excell như file đính kèm- Mình muốn lọc data cột C trong sheet1 ra cột H những số ĐT thõa mãn DK bắt đầu bằng 008491 & 008490- Sau đó lọc bỏ những SĐT trùng lặp ở cột H đi và chuyển về định dạng theo chuẩn SĐT thông thường thay vì có 008491xxxxx mà thay bắng 091xxxxx & 090xxxxxxx

 

[thaytu]

Bạn muốn nhờ làm dùm hay là muốn chỉ cho bạn biết cách làm ạ?

 

[htlamvdc]

thanks bạn thaytu đã quan tâm giúp mình.Bạn có thể hướng dẫn mình cách làm và làm giúp trên file Excel của mình luôn để mình lấy về nghiên cứu thêm nếu tự làm ko được. thanks bạn nhiều.

 

[thaytu]

Vấn đề những số ĐT thõa mãn DK bắt đầu bằng 008491 & 008490: Bạn tìm hiểu chức năng DATA, FILTER, AUTO FILTER lấy bắt đầu bằng thì chọn BEGIN WITH
Vấn đề lọc lấy dòng không trùng số đt thì thêm 1 cột phụ sau cột C, ô D2 gõ số 1, D3 =COUNTIF(C$2:C3,C3) và kéo xống
lúc này ra kết quả là số, nếu số 1 tức là số đt đó xuất hiện lần đầu từ trên xuống, 2, 3... thì xóa bỏ vì trùng.

 

[leonguyenz]

Đặt một Name lọc theo điều kiện:

dk=IF((LEFT(TRIM(Sheet1!$C$2:$C$84),6)="008490")+(LEFT(TRIM(Sheet1!$C$2:$C$84),6)="008491"),ROW(Sheet1!$C$2:$C$84),"")

Trích rút qua cột H, H2:

=IF(ROW(A1)<=COUNT(dk),"0"&RIGHT(TRIM(INDEX($C$2:$C$84,SMALL(dk,ROW(A1))-1,)),9),"")

Lọc duy nhất, (công thức mảng: kết thúc với tổ hợp phím Ctrl+Shift+Enter), I2:

=INDEX($H$2:$H$17,MATCH(0,COUNTIF(I$1:I1,$H$2:$H$17),0),)

 

[hieuxd]

Xin chào các cao thủ về Excell mình cũng ít khi dùng Excel và đang gặp phải vấn đề lọc data mong mọi người giúp với.- Mình có một bảng Excell như file đính kèm- Mình muốn lọc data cột C trong sheet1 ra cột H những số ĐT thõa mãn DK bắt đầu bằng 008491 & 008490- Sau đó lọc bỏ những SĐT trùng lặp ở cột H đi và chuyển về định dạng theo chuẩn SĐT thông thường thay vì có 008491xxxxx mà thay bắng 091xxxxx & 090xxxxxxx

Bạn dùng lọc Advanced nhé
Điều kiện lọc
=OR(LEFT(C4;7)=" 008491";LEFT(C4;7)=" 008490") có một dấu "cách" ở đầu trước hai số 00
khi lọc chọn recod only (lọc duy nhất luôn)
sau khi lọc bạn bôi đen vùng vừa lọc được nhấn Ctrl+h
Find nhập 0084 (có một dấu cách ở đầu)
Replace để trống
nhấn Replace All
Thêm số 0 ở đầu thì dùng định dạng Custom "0"0

 

[Ba Tê]

thanks bạn thaytu đã quan tâm giúp mình.Bạn có thể hướng dẫn mình cách làm và làm giúp trên file Excel của mình luôn để mình lấy về nghiên cứu thêm nếu tự làm ko được. thanks bạn nhiều.

Bạn muốn làm thử thì bạn làm theo từng bước nhé:
1/ Tô chọn từ C1:C84.
2/ Vào Data - Filter - Advanced Filter ... chọn Copy to another location, Copy to nhập $F$1 - Chọn Unique records only - OK
3/ Ô G2 nhập công thức: ="0" & MID(TRIM(F2);5;20)
Nếu không muốn thấy công thức nữa thì Copy cột G Paste Value lại tại chỗ, Xóa cột F, bạn đã có số ĐT bằng cách thay 4 số đầu bằng số 0.
Có khuyến mãi cho bạn thêm một Sub trong Module, cho Run sẽ có kết quả ở cột H.

Dim Dic As Object, sArr(), dArr(), I As Long, K As Long, Tem As String
Set Dic = CreateObject("Scripting.Dictionary")
sArr = Range([C2], [C65000].End(xlUp)).Value
ReDim dArr(1 To UBound(sArr, 1), 1 To 1)
For I = 1 To UBound(sArr, 1)
    Tem = Application.WorksheetFunction.Trim(sArr(I, 1))
    If Not Dic.Exists(Tem) Then
        K = K + 1
        Dic.Add Tem, K
        dArr(K, 1) = "'0" & Mid(Tem, 5, 20)
    End If
Next I
[H2].Resize(K).Value = dArr
Set Dic = Nothing
End Sub

 

[dhn46]

Bài này nếu dung Pivot Table (hoặc Data => Remove Duplicate) để trích lọc duy nhất rồi dùng công thức để chuyển đổi định dạnh số điện thoại là 1 cách dễ dàng cho người mới tiếp cận
Ngoài ra có thể dùng lập trình (ví dụ như ADO)
Bạn tham khảo File đính kèm, trong đó có sử dụng Pivot table + Công thức và ADO để giải quyết vấn đề của bạn

 

[Hai Lúa Miền Tây]

Thêm cách = ado.

Sub Loc_HLMT()
    Set adoConn = CreateObject("ADODB.Connection")
    Set adoRS = CreateObject("ADODB.Recordset")
    With adoConn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & ThisWorkbook.FullName & _
                            ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
        .Open
    End With
    With adoRS
        .ActiveConnection = adoConn
        .Open "select distinct '0' & right(f3,len(f3)-5) " & _
                "from [sheet1$a2:e1000] " & _
                "where left(F3,7) in (' 008491',' 008490')"
    End With
    With Sheet1
        .[H2:H1000].ClearContents
        .[H2].CopyFromRecordset adoRS
    End With
    adoRS.Close: Set adoRS = Nothing
    adoConn.Close: Set adoConn = Nothing
End Sub

 

[leonguyenz]

sau khi lọc bạn bôi đen vùng vừa lọc được nhấn Ctrl+h
Find nhập 0084 (có một dấu cách ở đầu)
Replace để trống
nhấn Replace All

Nếu có sdt 008491xx0084x thì sao nhỉ?
Phải ngăn ngừa trường hợp này.

 

[hungpecc1]

Xin chào các cao thủ về Excell mình cũng ít khi dùng Excel và đang gặp phải vấn đề lọc data mong mọi người giúp với.- Mình có một bảng Excell như file đính kèm- Mình muốn lọc data cột C trong sheet1 ra cột H những số ĐT thõa mãn DK bắt đầu bằng 008491 & 008490- Sau đó lọc bỏ những SĐT trùng lặp ở cột H đi và chuyển về định dạng theo chuẩn SĐT thông thường thay vì có 008491xxxxx mà thay bắng 091xxxxx & 090xxxxxxx

Có nhiều giải pháp, --> mình xin bổ sung thêm 1 giải pháp cho phong phú hoa lá cành :
[GPECODE=vb]
Sub GPE()
Dim cnn As Object, rst As Object
Dim SQL$
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=microsoft.ACE.OLEDB.12.0;" & _
"Data source=" & ThisWorkbook.FullName & _
";Extended properties=""Excel 12.0;HDR=No;IMEX=1"";"
.Open
End With
SQL = "SELECT DISTINCT REPLACE(f3,'0084','0') FROM [sheet1$] " & _
"WHERE f3 LIKE '%008491%' OR f3 LIKE '%008490%'"
rst.Open SQL, cnn, 3, 1, 1
[H2:H1000].ClearContents: [H2].CopyFromRecordset rst
rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]

Click vào nút button1 trong file đính kèm, xem kết quả thế nào !

 

[htlamvdc]

thanks KQ bạn leonguyenz rất ok đúng với mong muốn của mình. Thanks bạn nhiều, nhưng bạn giúp mình hướng dẫn chi tiết cách thức thực hiện với. Mình mò nãy giờ mà vẫn ko hiểu được để làm như bạn."Đặt một Name lọc theo điều kiện:Code:dk=IF((LEFT(TRIM(Sheet1!$C$2:$C$84),6)="008490")+(LEFT(TRIM(Sheet1!$C$2:$C$84),6)="008491"),ROW(Sheet1!$C$2:$C$84),"")cái này là đặt ở đâu vậy bạn ???

 

[hieuxd]

Nếu có sdt 008491xx0084x thì sao nhỉ?
Phải ngăn ngừa trường hợp này.

Em cũng đã nghĩ qua cái trường hợp này
Tùy vào dữ liệu cụ thể (dữ liệu thực để cho ra cách đơn giản nhất thôi anh a)
Trắc anh cũng biết dữ liệu ở đầu có 1 dấu cách nên anh dùng hàm "Trim" chuột để bỏ nó đi
Với dữ liệu của bạn ấy thì Nếu có sdt 008491xx0084x cũng không ảnh hưởng, kết quả vẫn trùng theo cách của anh Leo

 

[hieuxd]

thanks KQ bạn leonguyenz rất ok đúng với mong muốn của mình. Thanks bạn nhiều, nhưng bạn giúp mình hướng dẫn chi tiết cách thức thực hiện với. Mình mò nãy giờ mà vẫn ko hiểu được để làm như bạn."Đặt một Name lọc theo điều kiện:Code:dk=IF((LEFT(TRIM(Sheet1!$C$2:$C$84),6)="008490")+(LEFT(TRIM(Sheet1!$C$2:$C$84),6)="008491"),ROW(Sheet1!$C$2:$C$84),"")cái này là đặt ở đâu vậy bạn ???

Bạn nhấn F3 sẽ nhìn thấy nó

 

[Hai Lúa Miền Tây]

Bài này nếu dung Pivot Table (hoặc Data => Remove Duplicate) để trích lọc duy nhất rồi dùng công thức để chuyển đổi định dạnh số điện thoại là 1 cách dễ dàng cho người mới tiếp cận
Ngoài ra có thể dùng lập trình (ví dụ như ADO)
Bạn tham khảo File đính kèm, trong đó có sử dụng Pivot table + Công thức và ADO để giải quyết vấn đề của bạn

ADO: Thiếu điều kiện lọc.

Có nhiều giải pháp, --> mình xin bổ sung thêm 1 giải pháp cho phong phú hoa lá cành :
[GPECODE=vb]
Sub GPE()
Dim cnn As Object, rst As Object
Dim SQL$
Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=microsoft.ACE.OLEDB.12.0;" & _
"Data source=" & ThisWorkbook.FullName & _
";Extended properties=""Excel 12.0;HDR=No;IMEX=1"";"
.Open
End With
SQL = "SELECT DISTINCT REPLACE(f3,'0084','0') FROM [sheet1$] " & _
"WHERE f3 LIKE '%008491%' OR f3 LIKE '%008490%'"
rst.Open SQL, cnn, 3, 1, 1
[H2:H1000].ClearContents: [H2].CopyFromRecordset rst
rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing
End Sub
[/GPECODE]

Click vào nút button1 trong file đính kèm, xem kết quả thế nào !

Sẽ lọc thiếu chính xác nếu như trong số điện thoại có những số gần giống 008491 và 008490, hàm replace trên máy tôi không hoạt động được.

 

[hungpecc1]

Sẽ lọc thiếu chính xác nếu như trong số điện thoại có những số gần giống 008491 và 008490, hàm replace trên máy tôi không hoạt động được.

Cái này thì em biết , ban đầu định là LIKE ' 008491%' thôi !

còn cái vụ hàm REPLACE trên máy anh không hoạt động được <-------- cái này em không giải thích được ?, vì máy em vấn chạy được mà ()!

 

[Hai Lúa Miền Tây]

Cái này thì em biết , ban đầu định là LIKE ' 008491%' thôi !

còn cái vụ hàm REPLACE trên máy anh không hoạt động được <-------- cái này em không giải thích được ?, vì máy em vấn chạy được mà ()!

Hàm replace không sử dụng được do đâu thì bạn google sẽ thấy. Nếu sử dụng được đi chăng nữa thì cũng không nên dùng, vì nó sẽ replace những số giống có trong dãi số, ta phải kết hợp hàm left để replace, nhưng vừa left vừa replace thì ta dùng 1 hàm right cho gọn.

 

[Lê Duy Thương]

Bạn dùng lọc Advanced nhé
Điều kiện lọc
=OR(LEFT(C4;7)=" 008491";LEFT(C4;7)=" 008490") có một dấu "cách" ở đầu trước hai số 00
khi lọc chọn recod only (lọc duy nhất luôn)
sau khi lọc bạn bôi đen vùng vừa lọc được nhấn Ctrl+h
Find nhập 0084 (có một dấu cách ở đầu)
Replace để trống
nhấn Replace All
Thêm số 0 ở đầu thì dùng định dạng Custom "0"0

món này là thơm nhất ,đỉnh nhất.khoi
đau đầu

 

[yeudoi]

Để góp vui với mọi người mình cũng gửi lên đây một công thức nhưng sử dụng google spreadsheet mong mọi người chỉ xem đây là một cách để tham khảo
https://docs.google.com/spreadsheet/ccc?key=0AtuWUkMv4lmjdHZYdmtHTVh0OGpUQ3NYdVpzYTRodlE&usp=sharing