Compare two columns and add difference to separate column

Hi EE .. can someone help with this ?


I have a file with 100s of lines in the format below .. can someone help me so that column C is the result like likes below ?

I need the difference in Column C

Column A , Column B  , Column C
Name1  ,  Name1         , Name6
Name2  ,  Name2         ,Name7
Name3  ,  Name3        , Name8
Name4  , Name4        
Name5  , Name5
               , Name6
               , Name7
               , Name8
LVL 2
MilesLoganAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please give this a try.
In the attached, click the button "Get List' on Sheet1 to get the desired list in column C.

Sub GetList()
Dim x, y, z(), dict
Dim lr As Long, i As Long, j As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row
x = Range("A2:A" & lr).Value

lr = Cells(Rows.Count, 2).End(xlUp).Row
y = Range("B2:B" & lr).Value

Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(x, 1)
    dict.Item(x(i, 1)) = ""
Next i

For i = 1 To UBound(y, 1)
    If Not dict.exists(y(i, 1)) Then
        j = j + 1
        ReDim Preserve z(1 To j)
        z(j) = y(i, 1)
    End If
Next i
Columns(3).Clear
Range("C1").Value = "List"
Range("C2").Resize(j).Value = Application.Transpose(z)
End Sub

Open in new window

NameList.xlsm
0
 
oBdACommented:
You're going to have to do some more explaining here. Why is "Name6" the difference between "Name1" and "Name1"?
Based on your sample, it's impossible to determine what should go into column C based on which criteria.
0
 
MilesLoganAuthor Commented:
So column C is the difference between column A and B
0
On-Demand: Securing Your Wi-Fi for Summer Travel

Traveling this summer?Check out our on-demand webinar to learn about the importance of Wi-Fi security and 3 easy measures you can start taking immediately to protect your private data while using public Wi-Fi. Follow us today to learn more!

 
MilesLoganAuthor Commented:
Name 6,7,8 are not in column A
0
 
oBdACommented:
And what's the relation between Name6 and Name1?
Remember: you're currently the only one who knows how this file was created, what meaning the columns have, what the relations between the columns are, and what your intended outcome is.
Pretend you know exactly nothing about the file format, and then read your question again and try to understand what the asker might want.
Please, be a bit more specific.
Do you just want anything with an empty A column to appear in Column C in the order in which they appeared in Column B?
0
 
Ryan ChongCommented:
this can be done via Excel macro as well, like:

Sub test()
    Dim r1 As Range, r2 As Range, c As Range
    Dim r As Integer
    r = 2
    
    Set r1 = Range("A2", "A10")
    Set r2 = Range("B2", "B10")
    Range("C2", "C10").Clear
    
    For Each c In r2
        If Application.WorksheetFunction.CountIf(r1, c.Value) = 0 Then
            Cells(r, "C") = c.Value
            r = r + 1
        End If
    Next
End Sub

Open in new window

0
 
MilesLoganAuthor Commented:
sorry oBdA
0
 
MilesLoganAuthor Commented:
thank you both !
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.