Compare Columns and find missing data

[Revengerer]

HELP!

i'm trying to find a solution to the following problem.

i need to compare 2 columns and have the missing data place in column 3

eg

column 1, column 2, column 3
name1, name1, name2
name2, name4, name3
name3, name5
name4,
name5,

to try and explain a little more,

column1 has a full list of names and column2 has partial list of name. i want column3 to display the missing data

thanks in advance

 

[SydneyGeek]

Hi Revengerer, welcome to the Board!

one option:

Sub WriteMissingNames()
    Dim Rw As Long, Rw2 As Long
    Dim iFound As Integer
    
    Rw2 = 2
    For Rw = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        iFound = WorksheetFunction.CountIf(Range("B:B"), Cells(Rw, 1))
        If iFound = 0 Then
            Cells(Rw2, 3).Value = Cells(Rw, 1).Value
            Rw2 = Rw2 + 1
        End If
    Next Rw
    
End Sub

Place in a new module (alt+F11, insert>module, paste, alt+Q to return to Excel)
Run the code by pressing alt+F8 and double-clickign the macro name

Denis

 

[Revengerer]

WOW! thanks Denis for the quick reply. i didn't think anyone would know what i was talking about!

it works perfectly and give the exact results that i want

a few questions however...

1) is it possible to have this run automaticaly. idealy i would like it to be a formula that will auto update itself if a cell changes

2) can the columns be on different sheets? (sorry i'm new to macros)

thanks once again

 

[SydneyGeek]

1) is it possible to have this run automaticaly. idealy i would like it to be a formula that will auto update itself if a cell changes

Yes, you can do it using formulas but you will need a couple of helper columns.

With this layout:
First list starting in A2
Second list starting in B2

E2 has formula =COUNTIF(B:B,A2)
F2 has formula =IF(E2=0,COUNTIF($E$2:E2,0)+1,0)

Fill both of these down

Now the output is going into column C.
C2 has this formula:
=IF(ISNA(MATCH(ROW(),F:F,0)),"",INDEX(A:A,MATCH(ROW(),F:F,0),1))
Fill down

Denis

 

[Revengerer]

Thanks Denis!

works great! exactly what i needed

 

[missyfu]

I need help too. Too much data to sort for missing dates.
Can I find missing date together with name.

Example:
column 1 Column 2 Column 3
Alice Date 1 Date 1
Alice Date 2 Date 2
Alice Date 3 Date 3
James Date 2 Date 4
James Date 3 Date 5
James Date 5


Outcome:
Column1 Column2
Alice Date 4
Alice Date 5
James Date 1
James Date 4

I want to know the missing date for all the names.
In this case Alice didn't come on date 4 &5; James didn't turn up on date 1&4.
Can this be done?