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.
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.
ASKER
Hi ssaqibh,
Sounds good. Will try it out in the morning.
Thanks!
Rowby
Sounds good. Will try it out in the morning.
Thanks!
Rowby
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
ASKER
I'll upload a sample file in a minute.
ASKER
Here's an example spreadsheet. (Just a few fake records)experts-exchange
This is not a spreadsheet. It is the formulas as text.
ASKER
Ooops! Sorry. Wrong file. Here it is.
myster-master-for-experts-exchan.xlsx
myster-master-for-experts-exchan.xlsx
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
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
ASKER
Ah. I see now. I will try this later today.
ASKER
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
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
On the given worksheet I do not see the address 722 North Hayworth
either on sheet1 or on sheet2
either on sheet1 or on sheet2
ASKER
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
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
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.
ASKER
Thanks. Now that I've got some sleep and redid the formula -- it worked perfectly!
Thanks
Thanks
ASKER
Hello ssaqibh,
If you have time, please see the update to this question at:
https://www.experts-exchange.com/questions/28028981/Part-2-Comparing-difference-from-a-Main-Worksheet-with-other-worksheets-in-workbook.html
Thanks, Rowby
If you have time, please see the update to this question at:
https://www.experts-exchange.com/questions/28028981/Part-2-Comparing-difference-from-a-Main-Worksheet-with-other-worksheets-in-workbook.html
Thanks, Rowby
Enter this formula in a blank column row 2
=countif(Sheet2!C:C,C2)+co
Now filter this column using Data > Filter
select the zeros
copy the filtered rows to the new sheet