Two Sheets; On Comparison :)

[RRico]

Two Sheets; One Comparison

Problem:
Have data in two sheets named "Old" & "New."

Looking to compare both Column A's within both sheets respectively to determine what's missing on the "New" sheet after comparing against the "Old" sheet.

Rationale:
Every day, orders come across on a report and I'd like to show which orders were worked and therefore missing from the New report by comparing against the old report.

Hmph...:
Tried VLOOKUP and MATCH, but they don’t seem to quite give the “missing / difference” in the sense that I need to which/how many orders were worked from one day to the next.


Thanks to Anyone who thinks they know how to best show this... it seems simple, but I just can't figure it out :/

 

[Joe4]

Matching two lists is very easy with Microsoft Access. Just do an Unmatched Query to find the differences.
There is even a Wizard that walks you through this.

It should still be able to be done in Excel with VLOOKUP, though maybe not as easily.
I have a feeling that the "devil may be in some details" that you may be leaving out:

Rationale:
Every day, orders come across on a report and I'd like to show which orders were worked and therefore missing from the New report by comparing against the old report.

Hmph...:
Tried VLOOKUP and MATCH, but they don’t seem to quite give the “missing / difference” in the sense that I need to which/how many orders were worked from one day to the next.

This is where a sample of the data you are comparing and expected output would be most helpful, and posting exactly what you tried, and why it isn't working for you.

 

[RRico]

Re: Two Sheets; One Comparison

Matching two lists is very easy with Microsoft Access. Just do an Unmatched Query to find the differences.
There is even a Wizard that walks you through this.

It should still be able to be done in Excel with VLOOKUP, though maybe not as easily.
I have a feeling that the "devil may be in some details" that you may be leaving out:

This is where a sample of the data you are comparing and expected output would be most helpful, and posting exactly what you tried, and why it isn't working for you.

Hmm... Well, I'm not finding a wizard for this, and can't seem to find anything about Unmatched Query elsewhere online to assist in my endevor. I'm looking to attach my example, but I don't see a way to do so? Am I missing something? If I'm unable to attach, what would the formula look like (if it's easy to paste in here)? At present, the formula I'm using is:

=MATCH('Match Formula'!A2,'Match Formula'!A2:A43,)

 

[Joe4]

Re: Two Sheets; One Comparison

Hmm... Well, I'm not finding a wizard for this,

Which version of Access are you using?
In 2007, on the Create menu, under the Other ribbon, there is an icon for "Query Wizard", and the last option is "Find Unmatched Query Wizard".
I am pretty sure it is in the other versions too, but the menus may be structured a little differently.

If using Excel 2007, I would make use of the IFERROR function along with VLOOKUP, i.e.
=IFERROR(VLOOKUP(A2,'Match Formula'!A$2:A$43,1,0),"NOT FOUND!!!")

BTW, there are tools mentioned here that can be used to post screen images: http://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[RRico]

Re: Two Sheets; One Comparison

Thanks. I'm using 2007 for Access just as I am for Excel. Do I have to use Access for this solution? Boy, I rather not if possible.

I received a "circular reference" error when carrying your forumla down more than on the 1st Cell (A1). Any Ideas?

 

[Joe4]

Re: Two Sheets; One Comparison

I received a "circular reference" error when carrying your forumla down more than on the 1st Cell (A1). Any Ideas?

That is the danger in trying to just work off the solutions provided by the user, without really knowing any of the layout.
Can you explain the structure of your data?
What are your two sheet names?
Where does the data start/end on each sheet?
Which sheet are you placing the formula on?

 

[RRico]

Re: Two Sheets; One Comparison

That is the danger in trying to just work off the solutions provided by the user, without really knowing any of the layout.
Can you explain the structure of your data?
What are your two sheet names?
Where does the data start/end on each sheet?
Which sheet are you placing the formula on?

Problem:
There is a header row.
Have data in two sheets named "Old" & "New."
Looking to compare both Column A's within both sheets respectively to determine what's missing on the "New" sheet after comparing against the "Old" sheet.
I don't see the data ever going beyond A500.
The formula is actually residing in a third sheet named "Formula."

 

[Joe4]

Re: Two Sheets; One Comparison

So, then your formula would look something like this (to be placed in row 2 of your "Formula" sheet):
=IFERROR(VLOOKUP('New'!A2,'Old'!A$2:A$500,1,0),"NOT FOUND!!!")
and copy down for however many rows of data are on your "New" sheet.

 

[RRico]

Re: Two Sheets; One Comparison

So, then your formula would look something like this (to be placed in row 2 of your "Formula" sheet):
=IFERROR(VLOOKUP('New'!A2,'Old'!A$2:A$500,1,0),"NOT FOUND!!!")
and copy down for however many rows of data are on your "New" sheet.

I'm affraid this only gives me the results I once had when doing a simple compare and therefore shows what is "not found" on "new" based-off what's found on "old." Instead, what I seek is to find what orders have been worked and therefore missing/will no longer show on the "new" report, but did once show on the "old" report.

So, in other words, the new sheet will show new orders in column A along with other ones which haven't yet been worked. Though, the onces that have been worked because they're no longer on that report, but were on the "old" report are of interest to show how many were worked between then (old sheet cycle) and now (new sheet cycle).

I hope this helps... we're so close, I can feel it!

 

[Joe4]

Re: Two Sheets; One Comparison

If I am understanding you, then all you need to do is switch it around so that you are doing the VLOOKUP the other way, i.e.
=IFERROR(VLOOKUP('Old'!A2,'New'!A$2:A$500,1,0),"NOT FOUND!!!")
copy down for as many rows as entries you have on old your old sheet.