+ Reply to Thread
Results 1 to 6 of 6

Compare data across worksheets and copy data if match

  1. #1
    Registered User
    Join Date
    10-24-2011
    Location
    Orlando, FL
    MS-Off Ver
    Office 365 for Business
    Posts
    87

    Compare data across worksheets and copy data if match

    Hello:
    I am trying to compare data across two worksheets, and if the data in a particular row matches, copy data from second worksheet to the first worksheet. Not sure how to get started. My workbook is attached.
    Worksheet #1, Concierge Elite, contains customer orders. Worksheet #2, Carpet Pad Lookup contains a table that lists a bill of materials for a particular qty, width and depth of a customer order.

    If these records match:
    Sheet1, Column I = Sheet2, Column A
    and
    Sheet1, Column L = Sheet2, Column B
    and
    Sheet1, Column M = Sheet2, Column C

    If rows match across all columns, then I want the values in (Sheet2, Columns D-G) copied into (Sheet1, Columns N-Q).
    I am looking for a formula to make that happen.

    In the end, I want to find out how many of each color pad color I need to complete all orders. But, as long as that data is copied into Sheet1, I'm sure I could figure out a simple pivot table to do that part.

    I hope this makes sense!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Compare data across worksheets and copy data if match

    Hi,

    please check this example where VLookup is used with a Key column consisting of qty_width_depth: file export load list.xlsx
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Registered User
    Join Date
    10-24-2011
    Location
    Orlando, FL
    MS-Off Ver
    Office 365 for Business
    Posts
    87

    Re: Compare data across worksheets and copy data if match

    Thank you! This works great! I really appreciate it!

  4. #4
    Registered User
    Join Date
    10-24-2011
    Location
    Orlando, FL
    MS-Off Ver
    Office 365 for Business
    Posts
    87

    Re: Compare data across worksheets and copy data if match

    TehneXus helped me earlier with this:
    Now I have to add some requirements, and having trouble getting it to work again.
    If width, depth, quantity, Group, Subgroup, and Part match in both worksheets, I want value in column L of worksheet 1 to fill into column N of worksheet 2.

    For example, the highlighted rows in each worksheet match, as far as width, depth, quantity, Group, Subgroup, and Part. I would like "White, White" to appear in N127 of worksheet 2.

    My workbook is attached. Thank you!!
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Compare data across worksheets and copy data if match

    Hi,

    you have to make sure that the single parts the key consists of are in the exact same order in both sheets.
    In 2nd sheet "N2" paste:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-24-2011
    Location
    Orlando, FL
    MS-Off Ver
    Office 365 for Business
    Posts
    87

    Re: Compare data across worksheets and copy data if match

    Worked great! You are the best! Thank you.

+ 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. [SOLVED] Compare two columns and copy and paste data from a third column when a match is found
    By GatorsBucs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-28-2013, 06:54 PM
  2. [SOLVED] Compare data in columns of two worksheets and return value if match.
    By Zoediak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-02-2013, 10:38 AM
  3. Replies: 2
    Last Post: 02-28-2013, 04:45 PM
  4. Replies: 2
    Last Post: 06-07-2012, 07:03 AM
  5. Replies: 20
    Last Post: 01-02-2011, 10:00 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