How to align duplicates on the same rows in Excel

问题:

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.

回答1:

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

回答2:

Without 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.

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