删除基于两列组合和条件的重复行的宏

huangapple go评论67阅读模式
英文:

Macro to delete duplicate rows based on combination of two columns and a condition

问题

需要帮助创建一个宏来根据两列的组合(年份和LN列)删除重复行。在删除重复行时,只有当两行的价格不相同时,才会删除价格为0的行。

示例数据:

删除基于两列组合和条件的重复行的宏

在我的宏中,我无法检查“只有当两行的价格不相同时才删除价格为0的行”的条件。任何指导将不胜感激。

英文:

Need help to create a macro to delete duplicate rows based on combination of two columns (year and LN columns). While removing the duplicate rows, only the rows having price 0 to be removed if price value in both rows are not same.

Sample data:

删除基于两列组合和条件的重复行的宏

In my macro, I am not able to check for the condition "only the rows having price 0 to be removed if price value in both rows are not same".
Any guidance would be appreciated.

答案1

得分: 1

请使用以下代码。您应该证明自己进行了一些研究,至少提到并解决了一些问题...有许多处理此问题的代码片段,我只发布了一些,但删除第五列中包含零的条件可能是一些特殊情况。所以,我特例处理,希望您知道如果提出新问题应该做什么。代码假定标题位于第一行,并从"A:A"列开始。

Sub removeDuplicate3ColumnsCond()
  Dim sh As Worksheet, lastR As Long, arr, arrIt, i As Long, rngDel As Range, dict As Object
  
  Set sh = ActiveSheet '在此处使用您需要的工作表
  lastR = sh.Range("A" & sh.Rows.Count).End(xlUp).Row 'A:A列的最后一行
  arr = sh.Range("A1:E" & lastR).Value2 '将范围放入数组以进行更快的迭代
  
  Set dict = CreateObject("Scripting.Dictionary") '设置必要的字典,以保留唯一组合
  For i = 2 To UBound(arr)
    If Not dict.Exists(arr(i, 1) & arr(i, 2)) Then
        dict.Add arr(i, 1) & arr(i, 2), Array(arr(i, 5), i) '字典键作为列组合,项=数组(价格,行)
    Else
       If dict(arr(i, 1) & arr(i, 2))(0) = 0 Then           '如果第一次出现的价格为零
           addToRange rngDel, sh.Range("A" & dict(arr(i, 1) & arr(i, 2))(1)) '将行添加到要删除的范围
           arrIt = dict(arr(i, 1) & arr(i, 2))                                 '将字典项放入数组
           arrIt(1) = arr(i, 5): dict(arr(i, 1) & arr(i, 2)) = arrIt '更改价格并将修改后的数组放回
       Else
           addToRange rngDel, sh.Range("A" & i)      '将行放入要删除的联合范围
       End If
    End If
  Next i
  
  '选择要删除的行,以便进行可视检查:
  If Not rngDel Is Nothing Then rngDel.EntireRow.Select
  '如果选择的行都正确,请将上述行的“Select”替换为“Delete”
End Sub

从上面的评论中,我希望您理解,如其所示,该代码只选择要删除的行。如果您喜欢该选择,请将最后一行代码中的“Select”替换为“Delete”。您没有回答我的澄清问题,而我的代码假定可能会出现多次。它将删除价格为零(在E:E列中)的情况,但删除除一个之外的所有与价格相关的情况,如果不存在零价格,或者已经被放置在要删除的范围中。

英文:

Please, use the next code. You should prove that made some researches by your own, at least mention that and problems you faced... There are many pieces of code treating the issue, only I posted some, but condition to delete the ones having zero in the fifth column may be something peculiar. So, I am making an exception, hoping that you know what is to be done if place a new question. The code assumes that the headers are on the first row and starts from "A:A" column.

Sub removeDuplicate3ColumnsCond()
  Dim sh As Worksheet, lastR As Long, arr, arrIt, i As Long, rngDel As Range, dict As Object
  
  Set sh = ActiveSheet 'use here the sheet you need
  lastR = sh.Range("A" & sh.rows.count).End(xlUp).row 'last row on column A:A
  arr = sh.Range("A1:E" & lastR).Value2 'place the range in an array for faster iteration
  
  Set dict = CreateObject("Scripting.Dictionary") 'set the necessary dictionary, to kepp only unique combinations
  For i = 2 To UBound(arr)
    If Not dict.Exists(arr(i, 1) & arr(i, 2)) Then
        dict.Add arr(i, 1) & arr(i, 2), Array(arr(i, 5), i) 'dictionary key as columns combination, item = Array(Price, row)
    Else
       If dict(arr(i, 1) & arr(i, 2))(0) = 0 Then           'if the price of the first occurrence is zero
           addToRange rngDel, sh.Range("A" & dict(arr(i, 1) & arr(i, 2))(1)) 'add the row to the range to be deleted
           arrIt = dict(arr(i, 1) & arr(i, 2))                                 'place the dictionary item in an array
           arrIt(1) = arr(i, 5): dict(arr(i, 1) & arr(i, 2)) = arrIt 'change the price and drop back the modified array
       Else
           addToRange rngDel, sh.Range("A" & i)      'place the row in the Union range to be deleted
       End If
    End If
  Next i
  
  'select the rows to be deleted, in order to visually check them:
  If Not rngDel Is Nothing Then rngDel.EntireRow.Select
  'if the selected rows are OK, replace `Select` from the above line with `Delete`
End Sub

Reading the above comments, I hope you understood that the code, as it is, only selects the rows to be deleted. If you like that selection, you should replace Select with Delete in the last code line.

You did not answer my clarification question and my code assumes that there can be more than two occurrences. It will delete the zero price (in column E:E) cases, but all cases related to price, except one, if no zero price exists, or it has already been placed in the range to be deleted.

huangapple
  • 本文由 发表于 2023年4月4日 16:02:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/75926905.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定