Compare Sheet and Columns

[doku]

I am not sure if its a duplicate post.
I tried searching the forum, but couldn't relate it to the scenario I needed.

Book1
	A	B	C	D
1	Sheet1
2	Name	Age
3	a	10
4	b	20
5	c	30
6	d	40
7	e	50
8
9	Sheet2
10	Name	Age
11	b	20
12	c	30
13	d	40
14
15	Sheet3
16	Name	Age
17	a	notfound
18	b	matched
19	c	matched
20	d	didntmatch
21	e	notfound
Sheet1

I have three sheet. named Sheet1, Sheet2 and Sheet3.
Sheet1 being the master sheet. What I want to achieve is:
I want to search for Name in sheet1 with the list of names in Sheet2,
If the name is found, then compare the Age in Sheet1 and Sheet2. If the age matches make an entry in Sheet3 "matched" else "didnt match".
If the name itself is not found on sheet2 , make an entry saying not found.
I am not sure what would be the best approach to solve the problem(formula/VB)...
Hope I am clear with my query...
Can you all please help ?

 

[mfexcel]

Yes.... I got that....
The only confusion that I have now is....
If I want to compare a column index 5 in sheet1 and column index 8 in sheet2 and the result to be palced in column index 5 in sheet3.
Is that possible ?

Of course. Just change the Column Index in the formula accordingly. and then move the cell wherever you wish.

 

[jindon]

Yes.... I got that....
The only confusion that I have now is....
If I want to compare a column index 5 in sheet1 and column index 8 in sheet2 and the result to be palced in column index 5 in sheet3.
Is that possible ?

Sub test()
Dim a, b, i as Long, w(), e
With Sheets("sheet1")
    a = .range("e1", .Range("e" & rows.count).end(xlUp)).resize(,2).value
end with
With createobject("scripting.dictionary")
    .comparemode = vbtextcompare
    For i = 2 To ubound(a, 1)
        .item(a(i, 1)) = Array(a(i, 2), i)
    Next
    with Sheets("sheet2")
        b = .range("h1", .range("h" & rows.count).end(xlUp)).resize(, 2).value
    end with
    For i = 2 to UBound(b, 1)
        If .exists(b(i, 1)) then
            w = .item(b(i, 1))
            a(w(1), 2) = IIf(w(0) <> b(i, 2),"not ","") & "matched
            .remove b(i, 1)
        End if
    Next
    if .count > 0 then
        for each e in .items
            a(e(1), 2) = "not found"
        next
    end if
End with
Sheets("sheet3").cells(1, "e").resize(ubound(a,1), 2).value = a
End Sub

 

[doku]

I checked with both the approach.....
Both works fine for me.....
I am trying few more things on that....
will get back if I have any confusion...