Comparing difference from a Main Worksheet with other worksheets in workbook

Hello I have a mailing list in a workbook with a master list on Sheet 1
I also have Sheets 2 and Sheet 3 with portions of that same master list.

Not all of the mailing list has been copied onto sheets 2 and Sheets 3 and I want to find out which ones are missing so I can put them on Sheet 4

Only 1 column needs to be "Matched" and that would be street address.

So if the Formula (or Visual Basic) goes down the records on Sheet #1 and can't find it on Sheets #2 or SHeet #3 it needs to put it on Sheet Number 4

Does Excel (WIndows Excel 7) have a built in "comparison tool" that can do something like this?  Or is there, as I said, a formula or visual basic that can do this for me.

There are about 1000 records to go through.
LVL 9
Rowby GorenAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
=countif(Sheet2!E:E,C2)+countif(Sheet3!E:E,C2)
0
 
Saqib Husain, SyedEngineerCommented:
Assuming your street addrtess is in column C

Enter this formula in a blank column row 2

=countif(Sheet2!C:C,C2)+countif(Sheet3!C:C,C2)

Now filter this column using      Data > Filter

select the zeros

copy the filtered rows to the new sheet
0
 
Rowby GorenAuthor Commented:
Hi  ssaqibh,

Sounds good.  Will try it out in the morning.

Thanks!

Rowby
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rowby GorenAuthor Commented:
One clarification:

Sheet 1 has the address in one column  (Let's say column C)

Sheets 2 and Sheets 3 has the address in column E

How would your formula change....  (If at all)
Thanks

Rowby
0
 
Rowby GorenAuthor Commented:
Further clarification needed:

Sheet 1 is actually named Master
then comes  Sheets that are named by the default name:
Sheet 2
Sheet 3

So would that change the formula?  Do I have to use the exact names of the sheets in the formula? (I have a feeling your answer is "Yes, of course!"  So I will try that! :)

Rowby
0
 
Rowby GorenAuthor Commented:
Okay.  

I changed the worksheets to the proper worksheet name and indeed I ended up with a column of 0's.

When I copy it to a new sheet, they stay as zeros.

No addresses......

Rowby
0
 
Rowby GorenAuthor Commented:
I'll upload a sample file in a minute.
0
 
Rowby GorenAuthor Commented:
Here's an example spreadsheet.  (Just a few fake records)experts-exchange
0
 
Saqib Husain, SyedEngineerCommented:
This is not a spreadsheet. It is the formulas as text.
0
 
Rowby GorenAuthor Commented:
Ooops!  Sorry. Wrong file.  Here it is.
myster-master-for-experts-exchan.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
The formula is working just fine.

On the Master sheet select the dropdown in D1 and uncheck the 1
Click on OK
You will get a list with the zeros displayed and the ones hidden
Select the displayed rows
Copy these rows
Paste them to sheet 7
0
 
Rowby GorenAuthor Commented:
Ah.  I see now.   I will try this later today.
0
 
Rowby GorenAuthor Commented:
Hi ssaqibh,

I'm a little confused.  It works fine in the sample spreadsheet I uploaded.  But now when I copy the formula to another spreadsheet it's not working.  

Specifically I don't see any "1's" in the dropdown.

Please take a look at the attached.

For example, why isn't 722 Hayworth showing up as a "1??

Thanks!
myster-master-for-experts-exchan.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
On the given worksheet I do not see the address 722 North Hayworth
either on sheet1 or on sheet2
0
 
Rowby GorenAuthor Commented:
Hmmm.   I think I asked my original question slightly incorrectly.

Using the sample I uploaded, here's what I want to happen.

I have a column of addresses on the Master Sheet (including 722 North Hayworth).

I have addresses on Sheet 1 and on Sheet 2

If an address on Master Sheet (722 North Hayworth) does not appear on Sheet 1 or on Sheet 2 I want to be able to put those addresses on Sheet 3.

(If that's not easily done, I suppose I could consolidate all of my addresses that are currrently on Sheet 1 and Sheet 2 and use that as my Master Sheet -- and put my Master Sheet as Sheet 1 -- and still use your current formula.)

Rowby
myster-master-for-experts-exchan.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
You do not have to change anything. A 0 means not found whereas a 1 means found. You ca still use it to filter the zeros.
0
 
Rowby GorenAuthor Commented:
Thanks.  Now that I've got some sleep and redid the formula -- it worked perfectly!

Thanks
0
 
Rowby GorenAuthor Commented:
Hello ssaqibh,

If you have time, please see the update to this question at:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28028981.html

Thanks,  Rowby
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.