英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论