13

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.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
Niamh Doyle
  • 1,909
  • 7
  • 30
  • 42

2 Answers2

38

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
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • 1
    Superb! Your VBA code just does the trick. Great thanks to you. – Niamh Doyle Oct 11 '12 at 09:49
  • Though answer is pertinent to the question, It would be great if your sub procedure would have checked reversely and would have returned common-value between the columns. The values that are not common between the columns should be placed at the below or some alternative place. e.g. `col2` has new value which `col1` does not have, then that value of `col2` will be discarded using this formula/sub. – Learner Apr 29 '21 at 07:08
-1

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

LaBwork
  • 11