+ Reply to Thread
Results 1 to 18 of 18

Match between 2 sheets, highlight and transfer to new sheet

  1. #1
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Match between 2 sheets, highlight and transfer to new sheet

    Hi All,

    Thank you various supporting by so many good people in this forum for my earlier queries. Here i have another peculiar issue (may be for myself only). I have tried to find out various solutions, but could not implement properly. The requirement is explained below:

    I have two sheets with names "Download from Tool" and "Download from System".
    Both have unique ID in column "F".
    Rows in sheet "Download from Tool" starts from "A8" (Always), list of projects may go upto 100.
    Rows in sheet "Download from System" starts from "A2" (always), list of projects may go upto 100.
    *Note: sheet structures should remain as is. If named ranges are used for data handling, it would be nice for adjustment of data range which i am comfortable.

    1st Requirement is all unique ID's will have to be copied (from A to O from both sheets) to "Final Data" sheet.
    2nd requirement is for each unique id matched row, compare each cell of "Download from System" sheet w.r.t to same unique ID row in sheet "Download from Tool". Highlight the differences in "Download from System".

    Hope fully my explanation above is understandable. I am attaching work sheet example, in which i have highlighted few examples (Unique IDs, AP-26610006, AP-21310003, AP-21311016 ) in sheet "Download from System".

    Kindly suggest suitable code. Thanking you in advance

    Best Regards,
    Narasimharao
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Dear All,

    For more clarity, to copy unique data (requirement 2) i am using following solution at present.

    Look forward for suitable solution for requirement 1.

    best regards,

    Narasimharao

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Hi All,

    Sorry i made blunder in my previous post. The code posted was for requirement 1. Now i am waiting for solution against requirement 2 i.e.
    "for each unique id matched row, compare each cell of "Download from System" sheet w.r.t to same unique ID row in sheet "Download from Tool". Highlight the differences in "Download from System".

    Kindly help

    best regards,

    Narasimharao

  4. #4
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Dear All,

    Hope fully my first post was understandable. Looking for some help from experts.

    Thanking you in advance

    Best regards,
    Narasimharao

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Your request is not clear.
    Do you want to copy both sheets side by side or one below each other? I have done one below each other.
    The second request is not clear either. Is it that if they both match you want to highlight the sheet Download from System, but which column/s?
    Try the attached and need more clarity.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Hi AB33,

    Thank you once again. Yes your code is perfect for 1st requirement. Regarding second requirement, yeah "if they both match I want to highlight the sheet "Download from System". Columns to be matched are "from A to O" for every unique ID.

    Hope my explanation above is little more sensible.

    Best Regards,

    Narasimharao

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Match between 2 sheets, highlight and transfer to new sheet

    "Columns to be matched are "from A to O" for every unique ID".
    Do you mean you want to highlight columns A-O if there is a match in unique ID?

  8. #8
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Hi AB33,

    YES. Unique ID (column F) in both sheets "Download from Tool" and "Download from System" remain same. So where ever there is same unique ID, then respective row cells from A to O of sheet "Download from System" to be compared with matched row cells from A to O of sheet "Download from Tool". Unmatched cells have to be highlighted in "Download from System". For me this looks like combination of Vlookup as well match between several cells. So for me very difficult to build code with normal methods hence requested your support.

    Look forward for your suggestions.

    Best Regards,
    Narasimharao

  9. #9
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Match between 2 sheets, highlight and transfer to new sheet

    I am not sure I understand your request?
    Why you keep referring to column A-O for comparison purposes? If you are trying to compare each column with each column on both sheets, the attached is not the right code, but if you want to highlight missing items based on a single column F, the code is right.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Hi AB33,

    Sorry for delayed reply, yesterday i could not open the forum due to bad internet connectivity at home. regarding the requirement why i was referring columns "A to O"?. Currently the solution what you provided is highlighting entire row. but i wanted each cell which does not match has to get highlighted.

    Example:Unique ID: AP-21310003.
    Unique ID reference in sheet "Download from System" is "F8". The same Unique ID is available in sheet "Download from Tool" at "F13". Now comparision has to be made between each cell in the range "A8:O8" of Sheet "Download from System" with "A13:O13" of sheet "Download from Tool". "F8" can be skipped as this is the reference of comparision.

    Above to be compared for every Unique ID. Probably my expression was not clear earlier. I am little in experienced in expression of technical terms.

    Look forward for your suggestions.

    Best Regards,
    Narasimharao

  11. #11
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Having looked at the original attached again,I now understand what are you trying to do
    You not have only duplicates on the same row, but on the same column as well. Take for e.g N05315293 Modern MANUFAC
    appears in many rows and columns, so how you compare row by row if the same items appears in all rows?

  12. #12
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Hi AB33,

    Thanks for prompt reply again. If you carefully observe original attachment i have highlighted few cells where there is difference between the two sheets. Say example of

    Unique ID: AP-21310003.

    Once we find matching unique ID between both sheets, the comparision is with in that row for each of the cell. So for this unique ID, the cell comparisions between the two referred sheets are "G8 Vs G13, K8 Vs K13, M8 Vs M13, N8 Vs N13 and O8 Vs O13 etc etc". Where ever there is difference i have marked with color yellow in sheet "Download from System". Other cells do not have any differences hence not highlighted.

    So other rows which have "N05315293, Modern MANUFAC" repeatedly, need not be considered as they are pertaining to a different unique ID.

    Hopefully now there is more clarity.

    Best Regards,

    Narasimharao

  13. #13
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Match between 2 sheets, highlight and transfer to new sheet

    HI AB33 and every one,

    Looking for your help on above.

    Best Regards,

    Narasimharao

  14. #14
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,370

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Hi Narasimharao,
    maybe this code will help you with your second part (based on AB's code)
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Hi Nilem,

    Thank you for your reply. Code is getting stopped at "If .exists(Trim(y(i, 6))) Then". When checked it updates formula "=TRIM(F)" in column F of sheet 'Download from System' . There by no data to compare.

    Could you please review. Thanks again for prompt reply.

    best regards,

    Narasimharao

    VBA Error.JPG

  16. #16
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,370

    Re: Match between 2 sheets, highlight and transfer to new sheet

    here is your file,
    try

  17. #17
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Smile Re: Match between 2 sheets, highlight and transfer to new sheet

    Hi Nilem,

    Wow. wonderful, perfect!!!!. Thank you very much for the help. I am very much appreciated with your kindest support and helping me to improve my knowledge. Thanks again.

    Best Regards,
    Narasimharao

  18. #18
    Registered User
    Join Date
    04-23-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Match between 2 sheets, highlight and transfer to new sheet

    Hi AB33,

    I have re checked your code dated 22ndAug'13. That code is wonderful. Initially i have misunderstood. The code actually highlighting all matched data in "Download from System" and all differences between the two sheets are transferred to "Final Data" each line by line. Infact this is another way of analysis. That's Great!!! and i thank you once again.

    Best Regards,

    Narasimharao

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 03-11-2013, 05:37 AM
  2. Match unique cell reference in different sheets and highlight entire row using VBA
    By nickymac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-26-2012, 12:47 PM
  3. Replies: 0
    Last Post: 10-25-2012, 03:38 PM
  4. [SOLVED] Match or Loop to Transfer Data from Userform to Sheet
    By cschoyer in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-25-2012, 03:06 PM
  5. [SOLVED] VBA. One workbook, 9 sheets, how to transfer data from all sheets to one sheet?
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-08-2012, 01:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1