Compare Two Workbook for a KEY column 2nd having some extra rows & merge them

  • Hi


    Thanking you for taking interest. I have a small issue which I think can be solved easily by you experts. I have two different workbooks having some common columns like (First row is column heading in example)


    Work Book -1
    A__B__C__D__E__F__G


    QQ ZZ TT VV YY BB NN
    WW RR GG XX BB NN XX
    RR @@ %% FF VV BB TT


    Work Book - 2
    D__F__G


    VV BB NN
    VV AA KK
    XX NN XX
    FF BB TT


    As you can see both workbook have column D F & G where D is key column & contains identical data in both workbook . But workbook -2 contains two entries for VV. I want to merge the both sheets into a third which will have Macro/Function (whatever solution you may provide) to perform the action. The result sheet will look like as below



    A__B__C__D__E__F__G


    QQ ZZ TT VV YY BB NN
    _________VV ___AA KK
    WW RR GG XX BB NN XX
    RR @@ %% FF VV BB TT


    I have attached the sample.rar for reference in case you need to see the actual data sheet. I011RATB & TB02RACR are the workbook & TB02RACR-N is what I want as a result. Presently I have excel 2002 so please consider that while giving the solution.

  • Re: Compare Two Workbook for a KEY column 2nd having some extra rows & merge them


    singh.r,


    The new thread page is clear about cross posting. If you cross post you are required to provide a link to the other post.
    You cross posted here: Excel Help Forum


    Oddly, you did provide a link to this thread in your post on the other forum. Why not extend that courtesy to this forum?
    To understand why cross posting is not tolerated, click the link in my signature and read in full Message to Cross Posters.

  • Re: Compare Two Workbook for a KEY column 2nd having some extra rows & merge them


    Dear AAE


    I had read the rules but I think I misunderstood them. I thought I am supposed to provide the link in other posts only, but It appears that I am supposed to provide the cross post links here as well. If that be the case I'll take care in future. I understand the reasons why it's not tolerated. I am really sorry for that.

  • Re: Compare Two Workbook for a KEY column 2nd having some extra rows & merge them


    Hi singh.r,
    Would you have a test if this works?


    Regards, junho

  • Re: Compare Two Workbook for a KEY column 2nd having some extra rows & merge them


    Dear Junho


    Thank you very much for giving your precious time. As you told I tried, but you have not mentioned in which workbook I shall try so I tried by copying code in both data workbook one by one & a fresh workbook as well. In first two case I got error " Method range of object worksheet failed" & in New Workbook it says "Subscript out of range".
    Just mentioning it again If I have not been clear earlier that I need this code in third workbook only which will be empty one. TB02RACR is my 1st & main data which I want as it is in third workbook, & it contains one column GRNID where data is unique in column, I mean a GRNID is not repeated again. For each GRNID there is one ItemCD, ItemName & ItemQty column linked to it, all three columns exist in both workbook, but in second sheet I011RATB grnid may be repeating ie in TB02RACR grnid XXX is coming 1 time but in I011RATB grn id XXX may be coming 4 times so I want that in third workbook macro shall insert 3 extra rows below grnid XXX and fill the grnid,itemcd,itemname & itemqty from I011RATB. First occurrence of GRNID,ITEMCD,ITEMNAME & QTY will always match in both workbook so I just need to insert rest extra rows below & fill data & rest columns will remain blank.
    I hope I am clear, please tell if any further clarification is required. I know you must be very busy so thank you again for your help.

  • Re: Compare Two Workbook for a KEY column 2nd having some extra rows & merge them


    Dear Junho

    It's so humble of you to say sorry, it's the least I can do is wait when some one is giving me his helping hand for nothing. The macro worked like a charm. Thank you very much again. It's already served my purpose, but if you may sapre some more time I want to have little a modification. I want to copy one more colum data "mextrfno" which is in between mitemname & itemqty. I think I also can do it by going through your code & understanding it. I have one more issue that that if data is sorted due to any reason there will be no track that for which line the inserted rows belong to, so if you modify it a little bit to replicate entryno (1st column) & GRN Id to below cells then it'll be great. Thanking you again

    Regards
    Singh Raju

  • Re: Compare Two Workbook for a KEY column 2nd having some extra rows & merge them


    Dear Junho

    I don't have enough words to express my gratitude. I can just wish you all the best for everything in life.

    Regards
    Singh Raju

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!