Complex problem probably easy answer

[illfinduexl]

So I am new here and just can NOT for the life of me figure out how t
get this problem solved.

Situation I have a excel spread sheet that is formatted and can not b
sorted *its a long story just stick with we cant sort it* We will cal
it Datasheet 1
Datasheet 1 looks some thing like this

Name Address Zip Code
Bob 10 dog lane 12528 2
Andrew 27 mike street 18987 1
Stue 44 big road 45872 2
Frank 1 my street 55555 3

I then am given on a regular basis other excel sheets that are ver
random some have zips some don't , some have company name instead o
name. All sheets have at least a address and a Name * for this exampl
company name is the same as name* in summary these other sheets tha
come in are not sorted and have varying field types with at least nam
and address.

What I need to do is have a Sub script I can save that will run th
incoming sheets against datasheet1 find names that are on sheet2 but no
datasheet1 and write those To a third results sheet. Here is a exampl
keep in mind that the sheet named sheet2 is not the way it will all way
be formatted but it will all ways have name and address in column A an
B respectively. One of the biggest issues is that because nothing i
sorted or formatted I need to find a matching value from datasheet
column A any where in sheet2 column A

Datasheet 1
Name Address Zip Code
Andrew 27 mike street 18987 1
Bob 10 dog lane 12528 2
Stue 44 big road 45872 2
Frank 1 my street 55555 3

Sheet 2
Name Address Zip
Kyle 15 dog lane 12528
Andrew 27 mike street 18987
Alfred 8 lake drive 87456
Stue 44 big road 45872
Patrick 11 dog lane 11111
Frank 1 my street 55555

Out put should look like this

Kyle 15 dog lane 12528
Alfred 8 lake drive 87456
Patrick 11 dog lane 11111

Please keep in mind that the excel sheets ill be using are HUGE wit
over 15000 records so I can not just run a loop to check each cell.
will try and attach some excel examples along with this post. * Keep i
mind this has to be done with a macro/script because it needs to b
shared to other users and run able on new sheets

+-------------------------------------------------------------------
|Filename: ExcelExamples.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=462
+-------------------------------------------------------------------

 

[illfinduexl]

I realized a much easier way to say this I need to find matching cell
between two columns in different sheets and the rows are not matched u
as in there could be a value in cell A3 and I would need to find it eve
if its cell like A8 in the other sheet then i wanna move HOLE ROW t
another sheet

So I am new here and just can NOT for the life of me figure out how t
get this problem solved.

Situation I have a excel spread sheet that is formatted and can not b
sorted *its a long story just stick with we cant sort it* We will cal
it Datasheet 1
Datasheet 1 looks some thing like this

Name Address Zip Code
Bob 10 dog lane 12528 2
Andrew 27 mike street 18987 1
Stue 44 big road 45872 2
Frank 1 my street 55555 3

I then am given on a regular basis other excel sheets that are ver
random some have zips some don't , some have company name instead o
name. All sheets have at least a address and a Name * for this exampl
company name is the same as name* in summary these other sheets tha
come in are not sorted and have varying field types with at least nam
and address.

What I need to do is have a Sub script I can save that will run th
incoming sheets against datasheet1 find names that are on sheet2 but no
datasheet1 and write those To a third results sheet. Here is a exampl
keep in mind that the sheet named sheet2 is not the way it will all way
be formatted but it will all ways have name and address in column A an
B respectively. One of the biggest issues is that because nothing i
sorted or formatted I need to find a matching value from datasheet
column A any where in sheet2 column A

Datasheet 1
Name Address Zip Code
Andrew 27 mike street 18987 1
Bob 10 dog lane 12528 2
Stue 44 big road 45872 2
Frank 1 my street 55555 3

Sheet 2
Name Address Zip
Kyle 15 dog lane 12528
Andrew 27 mike street 18987
Alfred 8 lake drive 87456
Stue 44 big road 45872
Patrick 11 dog lane 11111
Frank 1 my street 55555

Out put should look like this

Kyle 15 dog lane 12528
Alfred 8 lake drive 87456
Patrick 11 dog lane 11111

Please keep in mind that the excel sheets ill be using are HUGE wit
over 15000 records so I can not just run a loop to check each cell.
will try and attach some excel examples along with this post. * Keep i
mind this has to be done with a macro/script because it needs to b
shared to other users and run able on new sheets

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Peter T]

You could do that manually -

On sheet2 in a helper column adjacent to the data

=COUNTIF('Datasheet 1'!$A$3:$A$6,A3)
change $A$3:$A$6 as required, or maybe A:A, or a named reference

copy the down (double click the little handle bottom right
You should get 0's for non duplicates and 1's or more if duplicates

Add a header to the helper column, eg "Dups"

Apply an autofilter
in the Dups filter, select 0
Copy the filtered range and paste to where required.

Obviously a macro could do similar, record one to get the basic syntax then
edit to remove all Selection/activate stuff and adapt to your own scenario.
If not sure how to go about that give more details, "other users and run
able on new sheets" is very vague.

Peter Thornton

PS, just had a quick look at your file, I see the sheet name is different
so -
=COUNTIF(Database!A:A,A2)