Solved

Comparing difference from a Main Worksheet with other worksheets in workbook

Posted on 2013-01-31
18
239 Views
Last Modified: 2013-02-12
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.
0
Comment
Question by:Rowby Goren
  • 12
  • 6
18 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38842178
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38842315
Hi  ssaqibh,

Sounds good.  Will try it out in the morning.

Thanks!

Rowby
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38844317
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 38844385
=countif(Sheet2!E:E,C2)+countif(Sheet3!E:E,C2)
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38844469
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38844483
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38844507
I'll upload a sample file in a minute.
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38844543
Here's an example spreadsheet.  (Just a few fake records)experts-exchange
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38844696
This is not a spreadsheet. It is the formulas as text.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 9

Author Comment

by:Rowby Goren
ID: 38844840
Ooops!  Sorry. Wrong file.  Here it is.
myster-master-for-experts-exchan.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38845761
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38849179
Ah.  I see now.   I will try this later today.
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38849358
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38849752
On the given worksheet I do not see the address 722 North Hayworth
either on sheet1 or on sheet2
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38849808
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38849863
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
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 38851068
Thanks.  Now that I've got some sleep and redid the formula -- it worked perfectly!

Thanks
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 38880693
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now