+ Reply to Thread
Results 1 to 16 of 16

Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not matched

  1. #1
    Registered User
    Join Date
    05-09-2012
    Location
    Long Beach
    MS-Off Ver
    Excel 2010
    Posts
    9

    Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not matched

    Hi Everyone,

    I have a problem that I am not sure how to solve. I have searched the forums and found some code that is close, but doesn't quite do what I need it to. Hopefully someone can help me out.

    Summary: I need to copy and overwrite rows of data from one sheet to another sheet but with a little logic. I want to look at the data in a specific column on both sheets. If there is a match, then I want the data from that specific row in the sheet 1 to overwrite the matching row in the sheet 2. If there isn't a match in the sheet2, I want the row from sheet 1 to be added to the bottom of sheet2. Lastly, I want to keep all entries in sheet 2 that do not have a match.

    Detail: I have attached a workbook with sample data. The data in sheet 1 (GWI022712) comes from a system that exports a CSV file. This sheet is updated every week and contains the latest data from a 6 month window. Column D has a unique ticket number that I want to try to match in sheet 2. Sheet 2 (GWI MASTER DATA) contains the same columns as sheet 1 and needs to be updated with the latest data from sheet 1. What I want to do is compare column D on both sheets and if a match is found, update the entire row on sheet 2 with the data from the matching row on sheet 1. New entries on sheet 1 that do not have a corresponding match on sheet 2 should be added to the bottom of sheet 2. Finally, older entries in sheet 2 that do not have a match (over 6 months old) should not be deleted. Obviously, I don't want to match the column header.

    I would really appreciate any help offered. Thanks!

    ~CS

    EDIT: Forgot one thing, I would like to reserve the last few columns on Sheet 2 for formulas so I don't want them to be deleted when the macro runs. Or I would need a way to quickly replicate them to the bottom of the sheet if they are overwritten.
    Attached Files Attached Files
    Last edited by Synchronicity; 05-10-2012 at 11:12 AM.

  2. #2
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    hi Synchronicity, welcome to ExcelForum, please check attachment, run code "test".

    The code actually recreates sort of .csv file so it would be more logical to have a file dialog option to select .csv file with data to import. If you approve this option, please provide original .csv file I will amend the code.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-09-2012
    Location
    Long Beach
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    Wow that was fast! This looks really promising! I like the idea of pulling from the file instead of having to make a new sheet. Let me test this out and see how it works. Thanks watersev!

  4. #4
    Registered User
    Join Date
    05-09-2012
    Location
    Long Beach
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    Hi watersev! Here are the new files. One is the CSV you requested, the other is a new master file. I have added two columns at the end of the master file that I will be populating with formulas later. It looks like the code does exactly what I need it to do! And as a bonus it doesn't appear to overwrite more columns than necessary so I am hoping the new columns for formulas are safe.

    Is it possible for the macro to copy cells in these new columns (the future formulas) to the end of the table when new rows are added? I was hoping to have a formula in column "I" that would search for the text "eai-incident" in column "H" and record a 1 if found and a 0 if not found. Then another simple formula in "J" that does some basic calculations. I was hoping the macro could copy them down and save me the manual step. Not sure if this is the right place to ask though.

    Anyway, I really appreciate the help. This will save me a lot of time.

    ~CS
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    please check attachment, press Run and choose csv-file to import data from. I've added some simple formula to I2:J2 to visualize that code extends existing formula till the last data row.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-09-2012
    Location
    Long Beach
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    Perfect! Thank you! This is exactly what I needed! I appreciate the effort.

  7. #7
    Registered User
    Join Date
    05-09-2012
    Location
    Long Beach
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    Spoke too soon. Tried it with some real data and got an error. Looks like it is related to their being too much data in the H, "Description" column cells. At least if I take the sample CSV and add several lines of text to that cell and copy down I can replicate the error with the dummy file. Here are some screen shots.Debug error.jpgRun-time error.jpg

  8. #8
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    as fas as I can tell basing on the screenshots provided is that either code has something left unaccounted or the data in csv file differs from sample file in layout

    It would be helpful to have file sample producing that error.
    Last edited by watersev; 05-16-2012 at 06:54 AM.

  9. #9
    Registered User
    Join Date
    05-09-2012
    Location
    Long Beach
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    I did tried converting to 2010 with no luck. I was able to modify the existing sample file to reproduce the error so I will provide that shortly along with what I did. Thanks again!

  10. #10
    Registered User
    Join Date
    05-09-2012
    Location
    Long Beach
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    Okay, now I am confused. I am uploading the three files I am using. Here is a brief description:

    SAMPLE OFFICIAL MASTER_UPDATED v3.xlsm - This is the file with the code. I updated the formulas and saved as an xlsm 2010 format.
    051612-FROM TAB-D.csv - This is a sample data file that fails.
    051612-FROM TAB-D-1.csv - This is the same file as above, but I copied cell H2 down about 1,000 rows.

    As noted, I updated the MASTER sheet to a 2010 format and made some changes to the formulas in columns I and J. I doesn't look like it has to do with the amount of data in the individual cells. This last export 051612-FROM TAB-D-1.csv I truncated that field so you will see it doesn't have a lot of data. However, importing the file fails. I tried to isolate if it was a particular cell in that column that was causing it to fail but didn't have any success. I changed 100 rows at a time and couldn't isolate it. But when I duplicate about 1,000 cells then the import succeeds. Does that make sense?

    So the second file 051612-FROM TAB-D-1.csv is just the first 1000 cells of that column copied down.

    SLA REPORT v3.zip

  11. #11
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    please check attachment (tested on the sample files provided)
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-09-2012
    Location
    Long Beach
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    This looks good so far. Still testing. Thanks a lot!

  13. #13
    Registered User
    Join Date
    05-09-2012
    Location
    Long Beach
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    All testing went well. Thanks again!

  14. #14
    Registered User
    Join Date
    06-13-2013
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    Thanks in advance.
    am looking same macro but am not able to capture any data here.

    Please can any one help on this how to Compare 2 Sheets & Copy Non Matching Data in separate sheet.

    Thanks
    Mohan

  15. #15
    Registered User
    Join Date
    10-26-2013
    Location
    dubai
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    Excel Problem
    Sheet 1...Received
    Date From Do NoS Item Qty Units
    10-Oct-13 XY Traders 321 Pencil 27 Nos
    11-Oct-13 AAB tools 2013 Grinder 2 Nos
    12-Oct-13 SS Traders 3098 Pen 27 Nos
    Sheet 2...Issued
    Date From Do No Item Qty Units
    11-Oct-13 XY Traders 321 Pencil 6 Nos
    12-Oct-13 AAB tools 2013 Grinder 1 Nos
    19-Oct-13 SS Traders 3098 Pen 13 Nos
    Sheet 3...Summary
    Date From / To Do No Item Received Issued Balance
    10-Oct-13 XY Traders 321 Pencil 27
    11-Oct-13 AAB tools 2013 Grinder 2
    11-Oct-13 XY Traders 241 Pencil 6
    12-Oct-13 SS Traders 3098 Pen 27
    12-Oct-13 AAB tools 2013 Grinder 1
    19-Oct-13 SS Traders 3098 Pen 13

    I need updated entry for sheet 3 date wise whatever enter sheet 1 and sheet 2

    Pls guide
    Regards
    raja

  16. #16
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Compare 2 sheets, match data in 1 column, update row in sheet 2 & add new rows not mat

    raja63,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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