1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Comparing data between two sheets excel 2007

Discussion in 'Business Applications' started by Kapil007, Dec 27, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Kapil007

    Kapil007 Thread Starter

    Joined:
    Dec 27, 2008
    Messages:
    17
    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
     
  2. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,863
  3. Kapil007

    Kapil007 Thread Starter

    Joined:
    Dec 27, 2008
    Messages:
    17
  4. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    Can you put an example file up on here so we can see what you are on about.
     
  5. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,863
    link works OK for me from the forum, if you scroll down a bit, you should see just after the words
    google ads

    followed by the macro
     
  6. Kapil007

    Kapil007 Thread Starter

    Joined:
    Dec 27, 2008
    Messages:
    17
    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:

  7. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    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:

  8. Kapil007

    Kapil007 Thread Starter

    Joined:
    Dec 27, 2008
    Messages:
    17
    Thanks villan it is working if i have other queries i will get back to you thanks thankyou very much.


    Kapil Gupta
     
  9. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    You are welcome Kapil. Glad it worked.
     
  10. Kapil007

    Kapil007 Thread Starter

    Joined:
    Dec 27, 2008
    Messages:
    17
    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:

  11. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    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.
     
  12. Kapil007

    Kapil007 Thread Starter

    Joined:
    Dec 27, 2008
    Messages:
    17
    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
     
  13. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    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.
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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?
     
  15. Kapil007

    Kapil007 Thread Starter

    Joined:
    Dec 27, 2008
    Messages:
    17
    Dear OBP,

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

    Thanks
    Regards
    Kapil007
     
  16. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/783673