+ Reply to Thread
Results 1 to 5 of 5

Matching multiple criteria from Sheet1 in Sheet2 and writing it in Sheet3

  1. #1
    Registered User
    Join Date
    07-07-2015
    Location
    Fairfax, VA
    MS-Off Ver
    2013
    Posts
    10

    Matching multiple criteria from Sheet1 in Sheet2 and writing it in Sheet3

    Hi All,
    I need to write a macro to takes legal contract number and item sku in sheet 1, and search in Sheet 2's license information and if found write all information in sheet3 from Sheet1 and Sheet2. Otherwise if it is not found in sheet2 at all, it will only writes sheet1 information in sheet3.
    Sheet3 will have all columns from SHeet1 and Sheet2 in that order.

    The purpose of this exercise to validate that all products are installed per legal contract per customer.
    I tried index and match however it didn't work.

    Please see details below.
    Any help is greatly appreciated.

    Sheet 1 - Service Contract Information
    • Sheet 1 contains service contract information including Contract ID, Customer Name, Legal Contract Number and Item sku number for columns A-D respectively.
    • In sheet1, there might be multiple legal contracts per account
    • In sheet 1, each contract might have many Item(SKU).
    • There are 3400 rows in this sheet.

    Sheet 2 - Product (Entitlement) Information
    • Sheet 2 contains list of products installed for customer account and includes Account Number, Account Name, License Information, Product Number, Product Name (Column A-F respectively)
    • In Sheet 2, column "license information" will have mixed values of legal contract number and Item Sku Numbers with also some additional text.
    • There are

    Logic
    For each row in Sheet 1, we need to take the (legal contract number and Item SKU number) from sheet1
    For each row in Sheet2, lookup (legal contract number and Item SKU number) in license information column.
    ○ If both are found in the cell then
    § Write all 4 columns from sheet 1 and 5 columns from Sheet2 row it into Sheet3
    End if

    ○ Process next row in Sheet 2 until all rows are processed.

    ○ If no match was found in sheet2,
    Write only the 4 columns from sheet 1 into sheet3
    GO to next row and process the same until all rows in sheet1 are processed.

    Notes:
    • For each record in sheet1 we can have multiple records in sheet2. Hence we need to write our output in sheet3 and not sheet1.
    • We need to keep record of all outcomes hence even if there is no match we will write the sheet1 row information in sheet3.

    Sheet 1
    Name:  Sheet1.jpg
Views: 218
Size:  55.3 KB


    Sheet 2

    Name:  Sheet2.jpg
Views: 245
Size:  144.8 KB

    Sheet 3
    Name:  Sheet3.jpg
Views: 245
Size:  178.8 KB
    Last edited by smgjob; 07-07-2015 at 09:31 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Matching multiple criteria from Sheet1 in Sheet2 and writing it in Sheet3

    Hi there... and welcome to the Excel Forum. If this is intended as a question, rather than a Tip or a Tutorial"; it's in the wrong place!! Also, you've attached a jpg, rather than a spreadsheet. That means that we have to guess what cells the data are in, and retype all this stuff before we can help. No-one is going to do that. I suggest you close this and repost in EITHER in the VBA section (if you want a VBA/macro solution) or in the General/Formulas section if you want a formula solution.

    At the very least - attach a sheet. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    The paperclip icon
    Glenn



  3. #3
    Registered User
    Join Date
    07-07-2015
    Location
    Fairfax, VA
    MS-Off Ver
    2013
    Posts
    10

    Re: Matching multiple criteria from Sheet1 in Sheet2 and writing it in Sheet3

    Thanks a million for your feedback.
    Sure. I will provide mockup in excel and reposted it.

  4. #4
    Registered User
    Join Date
    07-07-2015
    Location
    Fairfax, VA
    MS-Off Ver
    2013
    Posts
    10

    Re: Matching multiple criteria from Sheet1 in Sheet2 and writing it in Sheet3

    Dear Glenn, Thanks a million for your feedback.
    Sure. I will provide mockup in excel and reposted it.

  5. #5
    Registered User
    Join Date
    07-07-2015
    Location
    Fairfax, VA
    MS-Off Ver
    2013
    Posts
    10

    Re: Matching multiple criteria from Sheet1 in Sheet2 and writing it in Sheet3

    I have closed this post and reposted it to VBA/Macro section.
    http://www.excelforum.com/excel-prog...t=#post4124629

    This is not solved yet

+ 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: 8
    Last Post: 12-22-2013, 01:16 PM
  2. Matching names from sheet2, sheet3, sheet4 with names in sheet1
    By Lynx2x in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-28-2012, 07:51 AM
  3. Macro keeps looking for sheet1, won't use sheet2, sheet3 . . .
    By salvator in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-01-2011, 10:12 PM
  4. Macro to copy values from Sheet1 to Sheet3, based on criteria in Sheet2
    By John74 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2010, 04:55 PM
  5. A1 in sheet1 =” =SUM('sheet2:sheet3'!A1)”
    By minrufeng in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-22-2006, 03:10 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