Access Query vs. Comparing 2 Excel Spreadsheets 2

[kjv1611]

Okay, I may just be totally forgetting something here, but it seems to me that if you have 2 separate spreadsheets which you want to compare/add together, and you want to verify that they have the exact same reference IDs (such as Account IDs/Numbers), then the absolute easiest way to do so is to import the spreadsheets into Access, and run a query there.

Is there a way in Excel that would make that simpler? I may just be forgetting something, but I thought I would ask. There is no immediate need for this, particularly, but I would like to know going forward.

My first guess would be doing something with possibly MS Query, but I'm guessing that there might be another way built right into Excel that I just can't remember at the moment.

And just for this example, I'll make up some fake columns and data.

Let's say we have 2 data sets (currently in Excel), with the one identical column: FruitID. One sheet also has FruitName, and the other has FruitColor, and I want to combine the 2.

Sheet1:
FruitID FruitName
1111111 Apple
2222222 Orange
3333333 Grape
4444444 Tomato
5555555 Banana
6666666 Passion fruit
7777777 Water melon
8888888 Cantelope

Sheet2:
FruitID FruitColor
1111111 Apple
2222222 Orange
3333333 Grape
6666666 Passion fruit
7777777 Water melon
8888888 Cantelope

Now, in this example, the difference is obvious, but in real life, it aint so easy. at least not on occassion.

Now with that sample data or without, does anyone have any suggestions for me to review?

Thanks for any thoughts/suggestions/opinions.

--

"If to err is human, then I must be some kind of human!" -Me

 

[xlbo]

vlookup or Index/Match will tell you what records are in 1 file but not in the other

Or MS Query could easily be used

Or Access

To be honest, it's as much about preference as anything else. Unless your data set is massive, I would suggest that a simple vlookup would be the quickest way to determine missing vales between 2 data sets

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[sxschech]

If you want to do it with a formula, Paste the compare formula and it will tell you TRUE if it matches and FALSE if it doesn't match. Of course if the items are not lined up, you could get FALSE when the item may be there.

[TT]
Sheet1:
FruitID FruitName Compare Formula Evaluates to
1111111 Apple =A1=Sheet2!A1 TRUE
[/TT]

 

[SkipVought]

FYI, faq68-5829

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[kjv1611]

Thanks for all the suggestions. I have used vlookup before, just not often. I'll try and look into the suggestions sometime. However, I don't think the =A1=Sheet2!A1 type of formula would work, as mentioned that it would not work if the records were not lined up correctly. For instance, if the first say 1200 records were correct, but the next wasn't, then it would be possible that the remainder of records would all be off. But it is something interesting to tinker with, nonetheless.

Thanks for all the suggestions.

--

"If to err is human, then I must be some kind of human!" -Me

 

[xlbo]

kjv - exactly right on the non matching

Vlookup will take care of that - it will search all rows in teh sheet to see if any match

If your FruitIDs are in column A of sheet1 and column A of sheet2 then

=if(isna(vlookup(A2,Sheet2!$A$1:$A$10000,1,false)),"MISSING","")


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question