Variation on a Lookup/Match

[eds10]

I'm struggling with something that is seemling easy (also could not find it in any previous posts)...

I'm trying to do a variation of a lookup/match where I have information on two worksheets:

Sheet 1: has a list of values C3:C1002

Sheet 2: has a different list of values C6:C319

I would like to show the overlap of the two lists on the third worksheet in the in column C.

Any suggestions would be greatly appreciated. Thanks in advance for your help.

Erik

 

[venkat1926]

try something like this macro. modiffy to suit you

see comments in the code.

Sub test()
Dim rng1, cfind, c, dest As Range
Worksheets("sheet3").Columns("c:c").Delete
'the above line deletes columnC in sheet3
'this is only for teting purposes and can be removed later.
Worksheets("sheet1").Activate
Set rng1 = Range(Range("c1"), Range("c1").End(xlDown))
For Each c In rng1
With Worksheets("sheet2").Columns("c:c")
Set cfind = .Find(c.Value, lookat:=xlWhole)
If Not cfind Is Nothing Then
Set dest = Worksheets("sheet3").Cells(Rows.Count, "c").End(xlUp).Offset(1, 0)
c.Copy dest
End If
End With
Next
Worksheets("sheet3").Activate
End Sub

[/code]

 

[eds10]

Macro coming up blank...

Thanks for your help with the macro but unfortunately it is not returning any values:

Between Sheet 1 (C6:C1002) and Sheet 2 (C4:C319) there are probably about 150 that overlap.

Any thoughts?

 

[Aladin Akyurek]

I'm struggling with something that is seemling easy (also could not find it in any previous posts)...

I'm trying to do a variation of a lookup/match where I have information on two worksheets:

Sheet 1: has a list of values C3:C1002

Sheet 2: has a different list of values C6:C319

I would like to show the overlap of the two lists on the third worksheet in the in column C.

Any suggestions would be greatly appreciated. Thanks in advance for your help.

Erik

See my post in:

http://www.mrexcel.com/board2/viewtopic.php?t=144521

for a formula approach for extracting a list of common items.