Excel: Populate column C with differences between A and B

1
votes

I have 2 columns in 2 worksheets (WS1 & WS2) containing STRING value columns: A and B. I want to populate a third column "C" in a third worksheet (WS3) with the differences.

Put simply, I want to populate column WS3:C with item names that are in WS1:A but not in WS2:B.

1
vbaexcelexcel-formula
Much more easily with VBA - is that something you'd be happy to use? – CallumDA
Yes I'm quite familiar with VB (I write VB.NET ASP) – Digital Lightcraft
I misunderstood initially, I don't think you'd need VBA here – CallumDA

1 Answers

1
votes

You could actually do quite simply:

enter image description here

The formula in column C is

=IF(COUNTIF($B$1:$B$10,A1),"",A1)

Here is a VBA alternative:

Option Explicit

Sub DisplayUnique()
    Dim output As Range, r As Range
    Dim dict As Object

    Set dict = CreateObject("Scripting.Dictionary")

    Set output = ThisWorkbook.Worksheets("Sheet1").Range("C1")

    For Each r In ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
        If ThisWorkbook.Worksheets("Sheet1").Range("B1:B10").Find(r.Value, , , xlWhole) Is Nothing Then
            If Not dict.exists(r.Value) Then dict.Add r.Value, r.Value
        End If
    Next r

    output.Resize(dict.Count, 1).Value = Application.Transpose(dict.keys)
End Sub