vba 如何在Excel中的同一行上对齐重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12836396/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to align duplicates on the same rows in Excel
提问by Niamh Doyle
This is a simple question that I cannot answer.
这是一个我无法回答的简单问题。
I have two columns like these in Excel:
我在 Excel 中有两列这样的列：
Col1 Col2
A C
B I
C E
D D
E A
F F
G B
H
I
I want to sort the two columns so that the same values are aligned on the same rows in two columns, such as:
我想对两列进行排序，以便相同的值在两列的相同行上对齐，例如：
Col1 Col2
A A
B B
C C
D D
E E
F F
G
H
I I
K
So far, I have tried the following VBAcode:
到目前为止，我已经尝试了以下VBA代码：
Sub HighlightDups()
Dim i, LastRowA, LastRowB
LastRowA = Range("A" & Rows.Count).End(xlUp).Row
LastRowB = Range("B" & Rows.Count).End(xlUp).Row
Columns("A:A").Interior.ColorIndex = xlNone
Columns("B:B").Interior.ColorIndex = xlNone
For i = 1 To LastRowA
If Application.CountIf(Range("B:B"), Cells(i, "A")) > 0 Then
Cells(i, "A").Interior.ColorIndex = 36
End If
Next
For i = 1 To LastRowB
If Application.CountIf(Range("A:A"), Cells(i, "B")) > 0 Then
Cells(i, "B").Interior.ColorIndex = 36
End If
Next
End Sub
But this code just helps to find the duplicates and fails to put the duplicates on the same rows in the two columns.
但此代码仅有助于查找重复项，而未能将重复项放在两列中的相同行上。
I wonder if you guys can give a little help?
我想知道你们是否可以提供一点帮助？
Thanks a lot.
非常感谢。
回答by brettdj
without VBA
没有 VBA
- insert a blank column into column B
- in B1 put
=IF(ISNA(MATCH(A1,C:C,0)),"",INDEX(C:C,MATCH(A1,C:C,0)))and copy down - copy and paste back column B over itself as values to remove the formulae
- 在 B 列中插入一个空白列
- 在 B1 放下
=IF(ISNA(MATCH(A1,C:C,0)),"",INDEX(C:C,MATCH(A1,C:C,0)))并复制下来 - 将 B 列复制并粘贴回自身作为值以删除公式
In VBA
在 VBA 中
Sub Macro1()
Dim rng1 As Range
Set rng1 = Range([a1], Cells(Rows.Count, "A").End(xlUp))
rng1.Offset(0, 1).Columns.Insert
With rng1.Offset(0, 1)
.FormulaR1C1 = _
"=IF(ISNA(MATCH(RC[-1],C[1],0)),"""",INDEX(C[1],MATCH(RC[-1],C[1],0)))"
.Value = .Value
End With
End Sub
回答by LaBwork
Without VBA
没有 VBA
- in C1 put =VLOOKUP(A:A,B:B,1)
- if you have multiple columns, in E1 put =VLOOKUP(A:A,B:D,2) .... the last digit should change to 1(col B), 2(col C) 3(Col D) accordingly.
- 在 C1 中放置 =VLOOKUP(A:A,B:B,1)
- 如果您有多个列，则在 E1 中放置 =VLOOKUP(A:A,B:D,2) .... 最后一位数字应相应地更改为 1(col B), 2(col C) 3(Col D)。
You will have to copy and paste this for each array you are looking for in the separate columns accordingly, but you should be able to copy and paste down a column easily
您必须相应地在单独的列中为您要查找的每个数组复制和粘贴它，但您应该能够轻松地复制和粘贴一列
Hope this helps. Please let me know if you have any questions
希望这可以帮助。请让我知道，如果你有任何问题
