Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Comparing data between two sheets excel 2007


(!)

Kapil007's Avatar
Kapil007 Kapil007 is offline
Computer Specs
Junior Member with 17 posts.
THREAD STARTER
 
Join Date: Dec 2008
Experience: Advanced
27-Dec-2008, 04:23 AM #1
Exclamation Comparing data between two sheets excel 2007
Hi All,

i want to compare data between two work sheets of one workbook and if data matches then it will be copied to third work sheet automatically. can some body help me.

Thanks
Kapil
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,199 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
27-Dec-2008, 04:41 AM #2
i think this will do the job for you

http://exceltip.com/st/Compare_two_w...Excel/477.html
Kapil007's Avatar
Kapil007 Kapil007 is offline
Computer Specs
Junior Member with 17 posts.
THREAD STARTER
 
Join Date: Dec 2008
Experience: Advanced
29-Dec-2008, 01:01 AM #3
compairing data between two worksheets excel
http://exceltip.com/st/Compare_two_w...Excel/477.html

The link is not working please help.

Kapil
The Villan's Avatar
Member with 2,222 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
29-Dec-2008, 06:02 AM #4
Can you put an example file up on here so we can see what you are on about.
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,199 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
29-Dec-2008, 06:29 AM #5
link works OK for me from the forum, if you scroll down a bit, you should see just after the words
google ads

Quote:
With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences.
followed by the macro
Kapil007's Avatar
Kapil007 Kapil007 is offline
Computer Specs
Junior Member with 17 posts.
THREAD STARTER
 
Join Date: Dec 2008
Experience: Advanced
29-Dec-2008, 06:34 AM #6
Discription and sample file attached
Dear all,

I have an excel Sheet1 containg policyno in D3 to D500 coloum and sheet2 containg also the same data in D3 to D500 coloum and i want to compare the data in both the sheets and results shows in sheet3 and i want if policyno matchs then in next coloum of sheet3 in E3 coloum shows 0 if matches else shows n/a. can u please help me out. i am attaching the sample file.

thanks
kapil
Attached Files
File Type: xls PolicyandProposalDetails1.xls (93.0 KB, 2407 views)
The Villan's Avatar
Member with 2,222 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
29-Dec-2008, 07:21 AM #7
I have made some changes in your workbook.
I have convereted the policy numbers to numbers. They were text.


on the result sheet I have put the following formula down column G

=IF(AND(VLOOKUP(D4,DATA1!$D$3:$D$61,1,"FALSE"),VLOOKUP(D4,DATA2!$D$3:$D$61, 1,"FALSE")),0)

That seems to give you what you want as a result.
Attached Files
File Type: xls PolicyandProposalDetails1(1).xls (99.0 KB, 4087 views)
Kapil007's Avatar
Kapil007 Kapil007 is offline
Computer Specs
Junior Member with 17 posts.
THREAD STARTER
 
Join Date: Dec 2008
Experience: Advanced
29-Dec-2008, 08:39 AM #8
Thanks villan it is working if i have other queries i will get back to you thanks thankyou very much.


Kapil Gupta
The Villan's Avatar
Member with 2,222 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
29-Dec-2008, 12:48 PM #9
You are welcome Kapil. Glad it worked.
Kapil007's Avatar
Kapil007 Kapil007 is offline
Computer Specs
Junior Member with 17 posts.
THREAD STARTER
 
Join Date: Dec 2008
Experience: Advanced
30-Dec-2008, 05:32 AM #10
Post Re: compairing data between two worksheets excel
Dear villian,

you have given me below formula but it matches between D3of sheet 1 and D3 of sheet two, it is working fine.
=IF(AND(VLOOKUP(D4,DATA1!$D$3:$D$61,1,"FALSE"),VLOOKUP(D4,DATA2!$D$3:$D$61, 1,"FALSE")),0)

but suppose one value like 12443472 in D3, of sheet 1 and the same value is in D4 of Sheet 2 then how can we find out that comaprision.

actullay i want to seprate both the values that are matching or not matching. i am attaching the the new book for your reference.

Thanks
Regards
Kapil007
Attached Files
File Type: xls Book1.xls (29.5 KB, 1276 views)

Last edited by Kapil007; 30-Dec-2008 at 05:34 AM.. Reason: spelling mistake
The Villan's Avatar
Member with 2,222 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
30-Dec-2008, 06:45 AM #11
Kapil
The Vlookup checks all the numbers down column D of the first sheet By using FALSE, we force Excel to look at an exact match wherever that may be in the lookup table.
The Vlookup then checks all the numbers down column D of the second sheet By using FALSE, we force Excel to look at an exact match wherever that may be in the lookup table.

If it can find the exact match wherever that may be in each of the columns, it then returns a 0, otherwise #NA. This is based on the number that you are looking at in the third sheet (Results)

The new spreadsheet is nothing like your first example.Can you please stay with the original example, and not move the goalposts.

Basically, you have a column of numbers that are your standard. i.e. every number that is available (Results sheet)

You use those numbers in the Results Sheet to see if they occur both in DATA1 and DATA2 sheets. If they do, then 0 if not #NA

Unfortunately I have very little time today to deal with a new idea, but am perfectly willing to do that, if that is what you want, but it is more likely to be tomorrow.
Kapil007's Avatar
Kapil007 Kapil007 is offline
Computer Specs
Junior Member with 17 posts.
THREAD STARTER
 
Join Date: Dec 2008
Experience: Advanced
30-Dec-2008, 07:47 AM #12
Post compairing data between two worksheets excel
dear Villian,

i am berif you by below example:

Sheet 1 Sheet 2 Sheet 3
coloum A coloum A coloum A ColoumB coloumC
1 2254 2256
2 2257 2254
3 2284 2284
4 2278 2278
5 2288 2271
6 2271 2288
7 2281 2287
8 2265 2285

as shown above the number in sheet 1 and the number in sheet2 is suffuled, i want that vlookup function check between sheet1 A1:A8 and sheet2 A1:A8 and ;

i want that on the sheet 3 it will automatically copied the numbers of sheet1 to coloumA of sheet3 and numbers of sheet2 on coloumB of sheet3 and in the coloumC of sheet3 it shows the comparision and shows which number is repeating or not.

and i also want that

on the both sheet1 and sheet2 duplicate entries will highlighted with some color or some thing.

Thanks for taking intrest in solving my problem

hope now you will understand what i actually wanted

Thanks
regards
Kapil007
The Villan's Avatar
Member with 2,222 posts.
 
Join Date: Feb 2006
Location: Lincolnshire UK
30-Dec-2008, 01:07 PM #13
Had a quick look at what you are trying to do and I think, especially if this is going to be a regular job that you have to do, then the macro guru's need to look at this.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,569 posts.
 
Join Date: Mar 2005
Location: UK
31-Dec-2008, 07:21 AM #14
Kapil, in your example do you only want 2284 to be highlighted because they are equal and on the same row?
Or do you want all the duplicate numbers to be matched up with each other on the same rows?
Or do you want the third column to say which rows the duplicate numbers are on?
__________________
OBP
I do not give up easily
Kapil007's Avatar
Kapil007 Kapil007 is offline
Computer Specs
Junior Member with 17 posts.
THREAD STARTER
 
Join Date: Dec 2008
Experience: Advanced
05-Jan-2009, 01:08 AM #15
Post Re: Compairing data
Dear OBP,

i would be thankful to you if you provide me both the solutions. sorry for delay in reply.

Thanks
Regards
Kapil007
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑