Variation on a Lookup/Match

eds10

New Member
Joined
Sep 20, 2004
Messages
9
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
try something like this macro. modiffy to suit you

see comments in the code.
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

New Member
Joined
Sep 20, 2004
Messages
9
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,136,655
Messages
5,677,019
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top