Match And Copy Of 2 List Ranges

  • we have two workbooks which do change daily...


    one with 'data' sheet containing 40,000+ rows of data in coloumn 'B'
    another with 'cust' sheet with 10,000+ rows of data in coloumn 'C'


    as a part of daily work we have to compare last part of the each cell in Cust sheet with data sheet
    1. last 6 digits are compared and the results copied to 'cust' sheet from 'D' column and so on adjacent to the corresponding data.
    2. last 5 digits are compared and the results copied to 'cust' sheet from end of the aforesaid result (1)
    3. last 4 digits are compared and the results copied to 'cust' sheet from end of the aforesaid result (2)
    4. any duplicate within the row to be deleted from left to right


    as the data are enormous the work can be completed only 10 - 15 % manually


    masters here can help us out to get the result at accelerated pace through VBA


    thanks in advance :exclamat:
    kavi

  • Re: Match And Copy


    The answer is probably no, not enough details.


    Attached examples would help. Just limit them to 5 or 6 rows of data. Show a sheet with the results that you expect. You can make detailed explainations in the attachments to clarify what you need.

  • Re: Match And Copy


    thanks Mr.Kenneth Hobson
    here is my examble xls
    thanks to the code i got here. I am able to make last 5 digits be matched
    but
    1. i cant make the range flexible...
    2. It doest only match last 5 but also anywhere
    2. result is only restricted to 'C' coloumn not continues in 'D','E'... and so on (even if more matchs are there)

    Files

    • Market test.xls

      (23.55 kB, downloaded 73 times, last: )
  • Re: Match And Copy


    I added a Do loop for a repeat find and a line to place the found value into. I left your offset line and the alternative method for a single find as commented. You can remove them.


  • Match And Copy


    dear Kenneth Hobson
    Ur code helped me a lot !
    I modified that a bit to suit my needs.


    THANKS !


    still I need some improvements
    1. I had to redefine the Ranges as both of them vary daily
    2. If I need to get the result that matches right(5) from check sheet to right(5) from base sheet. (now right 5 in check sheet gets the match from anywhere from the cell) eg: jksdf45250 gets match from kg45250xy
    the delay in replying here is due to my try over ur solution. But I am not getting the right result
    thank u sir in advance