Merge list có điều kiện

[aurora4655]

Nhở các ace gỡ rối giúp cho mình với.

Mình có 2 cột giá trị trong Excel, mình muốn gộp 2 cột làm 1 với điều kiện là giá trị trong cột 1 lớn hơn reference cell thi lấy, nếu nhỏ hơn thì lấy giá trị ở cột 2.

*Số lượng cell trong mỗi cột thay đổi theo thời gian

ace xem thêm trong file đính kèm.

Xin cảm ơn.

 

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

Nhở các ace gỡ rối giúp cho mình với.

Mình có 2 cột giá trị trong Excel, mình muốn gộp 2 cột làm 1 với điều kiện là giá trị trong cột 1 lớn hơn reference cell thi lấy, nếu nhỏ hơn thì lấy giá trị ở cột 2.

*Số lượng cell trong mỗi cột thay đổi theo thời gian

ace xem thêm trong file đính kèm.

Xin cảm ơn.

Bạn nói ở list 1 điều kiện lấy là những số >1000 và list 2 điều kiện lấy <1000 vậy sao kết quả lại không phải như điều kiện bạn đặt ra?

 

[aurora4655]

Bạn nói ở list 1 điều kiện lấy là những số >1000 và list 2 điều kiện lấy <1000 vậy sao kết quả lại không phải như điều kiện bạn đặt ra?

View attachment 109249

Cảm ơn bạn đã trả lời. Mình xin lỗi vì đã đưa thông tin ví dụ thiếu chính xác. Trong ví dụ của mình thì giá trị cut point phải là 1100 mới chính xác.

 

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

Cảm ơn bạn đã trả lời. Mình xin lỗi vì đã đưa thông tin ví dụ thiếu chính xác. Trong ví dụ của mình thì giá trị cut point phải là 1100 mới chính xác.

Bạn dùng code sau:

[GPECODE=sql]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$3" Then
Dim lsSQL As String, cnn As Object, lrs As Object
Set cnn = CreateObject("ADODB.Connection")
Set lrs = CreateObject("ADODB.Recordset")
With cnn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=No;"";"
.Open
End With
With lrs
.ActiveConnection = cnn
lsSQL = "SELECT F1, 'Gauge 1' AS F2 FROM [Sheet1$B3:B1000] WHERE F1>" & Target.Value & _
" UNION ALL " & _
"SELECT F1, 'Gauge 2' AS F2 FROM [Sheet1$C3:C1000] WHERE F1<" & Target.Value
.Open lsSQL
End With
With Sheets("Sheet1")
.[E3:F1000].ClearContents
.[E3].CopyFromRecordset lrs
End With
lrs.Close: Set lrs = Nothing
cnn.Close: Set cnn = Nothing
End If
End Sub

[/GPECODE]
Mỗi khi thay đổi ở Cell D3 thì nó sẽ cho giá trị tương ứng.

 

[nhapmon]

Nhở các ace gỡ rối giúp cho mình với.

Mình có 2 cột giá trị trong Excel, mình muốn gộp 2 cột làm 1 với điều kiện là giá trị trong cột 1 lớn hơn reference cell thi lấy, nếu nhỏ hơn thì lấy giá trị ở cột 2.

*Số lượng cell trong mỗi cột thay đổi theo thời gian

ace xem thêm trong file đính kèm.

Xin cảm ơn.

thử giải bằng cthức.........................

 

[aurora4655]

Cảm ơn 2 bạn đã trả lời.
Tuy nhiên trong giải pháp bằng VBA, thì giá trị cut point không được hiển thị trong list mới, bạn chỉnh code thêm cho mình 1 chút nữa nha.

Trong giải pháp bằng công thức thì list mới bị lặp giá trị cutpoint nếu giá trị cut point cùng có 2 list ban đầu và list mới bị mất giá trị 0.

Nhờ các bạn giải hộ mình.

cảm ơn

 

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

Cảm ơn 2 bạn đã trả lời.
Tuy nhiên trong giải pháp bằng VBA, thì giá trị cut point không được hiển thị trong list mới, bạn chỉnh code thêm cho mình 1 chút nữa nha.

Trong giải pháp bằng công thức thì list mới bị lặp giá trị cutpoint nếu giá trị cut point cùng có 2 list ban đầu và list mới bị mất giá trị 0.

Nhờ các bạn giải hộ mình.

cảm ơn

Đó là do bạn diễn đạt lớn hơn và nhỏ hơn chứ đâu nói là lớn hơn hoặc bằng?
Chỉnh lại code thêm dấu = vào là được.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$3" Then
    Dim lsSQL As String, cnn As Object, lrs As Object
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    With cnn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & ThisWorkbook.FullName & _
                            ";Extended Properties=""Excel 8.0;HDR=No;"";"
        .Open
    End With
    With lrs
        .ActiveConnection = cnn
        lsSQL = "SELECT F1, 'Gauge 1' AS F2 FROM [Sheet1$B3:B1000] WHERE F1>[COLOR=#ff0000][B]=[/B][/COLOR]" & Target.Value & _
                " UNION ALL " & _
                "SELECT F1, 'Gauge 2' AS F2 FROM [Sheet1$C3:C1000] WHERE F1<[B][COLOR=#ff0000]=[/COLOR][/B]" & Target.Value
        .Open lsSQL
    End With
    With Sheets("Sheet1")
       .[E3:F1000].ClearContents
       .[E3].CopyFromRecordset lrs
    End With
    lrs.Close: Set lrs = Nothing
    cnn.Close: Set cnn = Nothing
End If
End Sub

 

[ndu96081631]

Đó là do bạn diễn đạt lớn hơn và nhỏ hơn chứ đâu nói là lớn hơn hoặc bằng?
Chỉnh lại code thêm dấu = vào là được.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$3" Then
    Dim lsSQL As String, cnn As Object, lrs As Object
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    With cnn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & ThisWorkbook.FullName & _
                            ";Extended Properties=""Excel 8.0;HDR=No;"";"
        .Open
    End With
    With lrs
        .ActiveConnection = cnn
        lsSQL = "SELECT F1, 'Gauge 1' AS F2 FROM [Sheet1$B3:B1000] WHERE F1>[COLOR=#ff0000][B]=[/B][/COLOR]" & Target.Value & _
                " UNION ALL " & _
                "SELECT F1, 'Gauge 2' AS F2 FROM [Sheet1$C3:C1000] WHERE F1<[B][COLOR=#ff0000]=[/COLOR][/B]" & Target.Value
        .Open lsSQL
    End With
    With Sheets("Sheet1")
       .[E3:F1000].ClearContents
       .[E3].CopyFromRecordset lrs
    End With
    lrs.Close: Set lrs = Nothing
    cnn.Close: Set cnn = Nothing
End If
End Sub

Dòng trên có >= rồi thì dòng dưới chỉ cần < thôi chứ nhỉ?
Vì mình nghĩ (theo logic) thì nếu cut point có mặt ở cả 2 list, chẳng lẽ ta lấy 2 lần

 

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

Dòng trên có >= rồi thì dòng dưới chỉ cần < thôi chứ nhỉ?
Vì mình nghĩ (theo logic) thì nếu cut point có mặt ở cả 2 list, chẳng lẽ ta lấy 2 lần

Cũng chưa chắc đâu Thầy ơi, có khi giá trị trên có mà dưới không có và ngược lại.

 

[ndu96081631]

Cũng chưa chắc đâu Thầy ơi, có khi giá trị trên có mà dưới không có và ngược lại.

Nếu vậy phải tốn thêm công đoạn xét trường hợp đăc biệt khi cut point có mặt ở cả 2 list. Khi ấy ta phải loại ra 1 thằng chăng?

 

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

Nếu vậy phải tốn thêm công đoạn xét trường hợp đăc biệt khi cut point có mặt ở cả 2 list. Khi ấy ta phải loại ra 1 thằng chăng?

Vấn đề là ta bỏ thằng nào, 1 hay là 2. Thêm 1 điều kiện xét là nếu nó có tồn tại ở list nào thì sẽ không xuất hiện ở list còn lại.

 

[cobecan1201]

Bạn nhapmon ơi cho mình hỏi file GPE mình thấy bạn có dùng công thức =IF(COUNT(ExtractList1)+COUNT(ExtractList2)>ROWS($1:1),INDEX((list1,list2),SMALL(IF(ROWS($1:1)<=COUNT(ExtractList1),ExtractList1,ExtractList2),MOD((ROWS($1:1)-1),COUNT(ExtractList1))+1),,INT(ROWS($1:1)/(COUNT(ExtractList1)+1))+1),"")

Cho mình hỏi Extract là gì vậy bạn. Chỉ cho mình với nhe. Cảm ơn cả nhà.

 

[nhapmon]

Bạn nhapmon ơi cho mình hỏi file GPE mình thấy bạn có dùng công thức =IF(COUNT(ExtractList1)+COUNT(ExtractList2)>ROWS($1:1),INDEX((list1,list2),SMALL(IF(ROWS($1:1)<=COUNT(ExtractList1),ExtractList1,ExtractList2),MOD((ROWS($1:1)-1),COUNT(ExtractList1))+1),,INT(ROWS($1:1)/(COUNT(ExtractList1)+1))+1),"")

Cho mình hỏi Extract là gì vậy bạn. Chỉ cho mình với nhe. Cảm ơn cả nhà.

đó là name đó bạn, nhấn Ctrl F3 để xem các name (nó là các cthức--->đưa vào trong name để nhìn cthức cho gọn vậy mà)
ExtractList1=IF(list1>=Sheet1!$D$3,ROW(list1)-2,"")
ExtractList2=IF(list2<Sheet1!$D$3,ROW(list2)-2,"")

thật ra thì giải cho vui thôi, chứ bài toán phức tạp như vậy nên theo hướng code.

......................................
cũng với tinh thần vui là chủ yếu
gởi lại cho chủ xị, sau khi đã gọt tỉa công thức

 

[aurora4655]

Cảm ơn các bạn đã giải đáp dùm mình. hiện tại vấn đề của mình đã được giải quyết nhờ giải pháp của bạn nhapmon. Mình thấy cách giải bằng VBA rất hay, nhưng giải bằng công thức ở đây đơn giản hơn và gọn hơn.

Trong cách của bạn nhập môn mình thấy rất hay ở chỗ bạn đặt name cho extractlist.

Cảm ơn các bạn