I have two columns in a excel sheet as shown below.

Column A    Column B
87            87
107           107
108           108
117           117
119           119
153           153
170           170
174           174
209           209
409106386989  409014007285
409110099164  174

Here both columns A and B contains 11 entries.
Now I have to find out the non identical entries from both columns.
i.e entries 409106386989 and 409110099164 are present only in Column A but not in B.
So I have to separate these entries from Column A.

Like wise entries 409014007285 present only in column B but not in A.
Also there are two entries 174 in column B but only one entry in column A.
So I have to separate these two entries i.e 409014007285 and 174 from column B.

So the Output should be as below.

    Column A               Column B       A but not B        B but not A
          87                     87      409106386989                174
         107                    107      409110099164       409014007285
         108                    108
         117                    117
         119                    119
         153                    153
         170                    170
         174                    174
         209                    209
409106386989           409014007285
409110099164                    174

I am doing this process manually which is taking much time when the entries are thousand togather.

Is it possible to do this by running some code (Using V B scripting)...?

closed as unclear what you're asking by Dave, Raystafarian, Sathyajith Bhat May 24 '14 at 8:21

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • What have you tried so far? The question was terribly formatted (well done to @DevonParsons for a good edit) and reads like you've made no effort. – Dave Apr 24 '14 at 11:52
  • Additionally, are the pairs formatted as pairs or would that need to be done as well? We can't help you solve a specific problem unless you have a specific problem with the methods you've already tried. Perhaps give us some background on why you're stuck. – Raystafarian Apr 24 '14 at 12:56
  • Structured references in Excel Tables & the programming language AutoIt comes with an excel.au3 library. – Mr. Hargrove Apr 24 '14 at 13:25

Give this a try:

Sub GetUnique()
    Dim N As Long, M As Long, i As Long, K As Long, L As Long
    Dim r As Range
    N = Cells(Rows.Count, "A").End(xlUp).Row
    M = Cells(Rows.Count, "B").End(xlUp).Row

    K = 1
    For i = 1 To N
        v = Cells(i, "A").Value
        Set r = Range(Cells(1, 2), Cells(M, 2)).Find(v)
        If r Is Nothing Then
            Cells(K, "C").Value = v
            K = K + 1
        End If
    Next i

    K = 1
    For i = 1 To M
        v = Cells(i, "B").Value
        Set r = Range(Cells(1, 1), Cells(N, 1)).Find(v)
        If r Is Nothing Then
            Cells(K, "D").Value = v
            K = K + 1
        End If
    Next i

End Sub


This macro processes data in columns A & B. The output data is placed in columns C & D.

To learn more about macros in general, see:




Macros must be enabled for this to work!


This macro, similar to the first, will handle multiple occurrences of the same value in both columns A & B

Sub FindNonDuplicates()
    Range("A:A").Copy Range("C1")
    Range("B:B").Copy Range("D1")
    Dim nC As Long, nD As Long, v As Variant
    Dim i As Long, j As Long
    nC = Cells(Rows.Count, "C").End(xlUp).Row
    nD = Cells(Rows.Count, "D").End(xlUp).Row

    For i = nC To 1 Step -1
        v = Cells(i, "C").Value
        For j = 1 To nD
            If v = Cells(j, "D").Value Then
                Cells(i, "C").Delete Shift:=xlUp
                Cells(j, "D").Delete Shift:=xlUp
                nD = nD - 1
                GoTo qwerty
            End If
        Next j
    Next i
End Sub
  • 1
    What does this code do? How does it work? How would one execute it and/or modify it if they had a different structure? Expanding on what the code does makes it more useful for someone who isn't well-versed with VBA. – Raystafarian Apr 28 '14 at 13:05
  • @Raystafarian Thank you for your comments! I will EDIT the answer to supply more information – Gary's Student Apr 28 '14 at 13:22
  • @DaveRook You are correct!! I will look into fixing this bug! – Gary's Student Apr 28 '14 at 14:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.