如何在Excel中将重复对齐在同一行上 [英] How to align duplicates on the same rows in Excel
本文介绍了如何在Excel中将重复对齐在同一行上的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是一个简单的问题,我无法回答。
我在Excel中有两列这样的列:
Col1 Col2
AC
BI
CE
DD
EA
FF
GB
H
I
我想对两列进行排序,以使相同的值对齐在两列中的相同行上,例如:
Col1 Col2
/ pre>
AA
BB
CC
DD
EE
FF
G
H
II
K
到目前为止,我已经尝试了以下
VBA
代码:Sub HighlightDups()
pre>
Dim i,LastRowA,LastRowB
LastRowA = Range(A& Rows.Count).End(xlUp ).Row
LastRowB = Range(B& Rows.Count).End(xlUp).Row
列(A:A)。Interior.ColorIndex = xlNone
列( B:B)。Interior.ColorIndex = xlNone
对于i = 1 To LastRowA
如果Application.CountIf(Range(B:B),Cells(i,A))> ; 0然后
Cells(i,A)。Interior.ColorIndex = 36
End If
下一个
对于i = 1 To LastRowB
如果Application.CountIf范围(A:A),单元格(i,B))> 0然后
Cells(i,B)。Interior.ColorIndex = 36
End If
Next
End Sub
但是这个代码只是帮助找到重复的对象,而不能将重复项放在两列中的相同行上。
我想知道你们可以给一点帮助吗?
非常感谢
解决方案/ strong>
- 在B1中插入一个空白栏
- 在B1中
= IF(ISNA(MATCH(A1,C:C,0)),,INDEX(C:C,MATCH(A1,C:C,0)))
并复制 - 将列B复制并粘贴到自己身上,作为删除公式的值
在VBA中
Sub Macro1()
pre>
Dim rng1 As Range
Set rng1 =范围([a1],Cells(Rows.Count,A)。End(xlUp))
rng1.Offset(0,1).Columns.Insert
使用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 Sub
This is a simple question that I cannot answer.
I have two columns like these in 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
VBA
code: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.
解决方案without 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
In 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
这篇关于如何在Excel中将重复对齐在同一行上的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文