[illfinduexl]

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

Situation I have a excel spread sheet that is formatted and can not be sorted *its a long story just stick with we cant sort it* We will call 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 very random some have zips some don't , some have company name instead of name. All sheets have at least a address and a Name * for this example company name is the same as name* in summary these other sheets that come in are not sorted and have varying field types with at least name and address.

What I need to do is have a Sub script I can save that will run the incoming sheets against datasheet1 find names that are on sheet2 but not datasheet1 and write those To a third results sheet. Here is a example keep in mind that the sheet named sheet2 is not the way it will all ways be formatted but it will all ways have name and address in column A and B respectively. One of the biggest issues is that because nothing is sorted or formatted I need to find a matching value from datasheet1 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 with over 15000 records so I can not just run a loop to check each cell. I will try and attach some excel examples along with this post. * Keep in mind this has to be done with a macro/script because it needs to be shared to other users and run able on new sheets*

[illfinduexl]

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

Originally Posted by illfinduexl

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

Situation I have a excel spread sheet that is formatted and can not be sorted *its a long story just stick with we cant sort it* We will call 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 very random some have zips some don't , some have company name instead of name. All sheets have at least a address and a Name * for this example company name is the same as name* in summary these other sheets that come in are not sorted and have varying field types with at least name and address.

What I need to do is have a Sub script I can save that will run the incoming sheets against datasheet1 find names that are on sheet2 but not datasheet1 and write those To a third results sheet. Here is a example keep in mind that the sheet named sheet2 is not the way it will all ways be formatted but it will all ways have name and address in column A and B respectively. One of the biggest issues is that because nothing is sorted or formatted I need to find a matching value from datasheet1 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 with over 15000 records so I can not just run a loop to check each cell. I will try and attach some excel examples along with this post. * Keep in mind this has to be done with a macro/script because it needs to be shared to other users and run able on new sheets*

[Peter T[_5_]]

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)


"illfinduexl" wrote in message
...

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

Situation I have a excel spread sheet that is formatted and can not be
sorted *its a long story just stick with we cant sort it* We will call
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 very
random some have zips some don't , some have company name instead of
name. All sheets have at least a address and a Name * for this example
company name is the same as name* in summary these other sheets that
come in are not sorted and have varying field types with at least name
and address.

What I need to do is have a Sub script I can save that will run the
incoming sheets against datasheet1 find names that are on sheet2 but not
datasheet1 and write those To a third results sheet. Here is a example
keep in mind that the sheet named sheet2 is not the way it will all ways
be formatted but it will all ways have name and address in column A and
B respectively. One of the biggest issues is that because nothing is
sorted or formatted I need to find a matching value from datasheet1
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 with
over 15000 records so I can not just run a loop to check each cell. I
will try and attach some excel examples along with this post. * Keep in
mind this has to be done with a macro/script because it needs to be
shared to other users and run able on new sheets*


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



--
illfinduexl