如何在Excel中的同一行上对齐重复项 [英] How to align duplicates on the same rows in Excel

问题描述

这是一个我无法回答的简单问题.

This is a simple question that I cannot answer.

我在 Excel 中有两列这样的:

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       

到目前为止，我已经尝试了以下 VBA 代码:

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?

非常感谢.

推荐答案

没有 VBA

• 在 B 列中插入一个空白列
• 在 B1 中放入 =IF(ISNA(MATCH(A1,C:C,0)),"",INDEX(C:C,MATCH(A1,C:C,0))) 并复制下来
• 将 B 列复制并粘贴回自身作为值以删除公式

在 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屋！