[Kapil007]

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]

i think this will do the job for you

http://exceltip.com/st/Compare_two_w...Excel/477.html

[Kapil007]

http://exceltip.com/st/Compare_two_w...Excel/477.html

The link is not working please help.

Kapil

[The Villan]

Can you put an example file up on here so we can see what you are on about.

[etaf]

link works OK for me from the forum, if you scroll down a bit, you should see just after the words
google ads

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]

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

[The Villan]

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.

[Kapil007]

Thanks villan it is working if i have other queries i will get back to you thanks thankyou very much.


Kapil Gupta

[The Villan]

You are welcome Kapil. Glad it worked.

[Kapil007]

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

[The Villan]

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]

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]

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]

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?

[Kapil007]

Dear OBP,

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

Thanks
Regards
Kapil007