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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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]
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,218,576
Messages
6,143,317
Members
450,477
Latest member
teresab543

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
Back
Top