+ Reply to Thread
Results 1 to 3 of 3

Locate text with in text string and run "IF" commands on the row in the text located

  1. #1
    Registered User
    Join Date
    02-07-2019
    Location
    New York
    MS-Off Ver
    Office 365
    Posts
    21

    Locate text with in text string and run "IF" commands on the row in the text located

    Hi,
    I have a product catalog, as an example lets say its for shoes.
    column B holds the model number (Handle), column C holds the Name (Title).
    there are 3 different types of products:
    Regular products - For example: Women stiletto heels brown color size 6
    Master products - For example: Nike model 270C
    Variant products - For example: Nike model 270C - Brown size 8

    Master products are not "real" items, they are the "Master" model. think of purchasing shoes online, you can not purchase "Nike model 270C", it needs to be more specific... You can purchase: Nike model 270C - Brown size 8
    The Variant products are the "real" items under the Master product.

    I need help in finding a way of locating the Master products and their Variants. then giving each Variant product the model number (Handle) of its own Master.

    In the below image, the Variant product (line 4) should have the Handle of its Master product (cell A3)
    the only way to know that an item (a line in the excel) is a Variant item that has a Master (Vs a regular product) is by its Title which is identical to its Master just longer. the Variant product title Always as a "-" followed by the specific color/model/size
    Example:
    Nike model 270C
    Nike model 270C - Brown size 8

    Screen Shot 2019-02-12 at 11.08.09.jpg


    Attached sample .xlsx with just over 20 lines of sample data. its missing more than the above but once I understand how to do the above I think i can manage the rest.

    For anyone downloading the sample .xlsx - The part I need assistance with is in sheet "READY".

    Thank you for taking the time reading the above.
    Any help is appreciated.

    Ziv
    Attached Files Attached Files
    Last edited by Zivhodiva; 02-12-2019 at 06:50 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Locate text with in text string and run "IF" commands on the row in the text located

    For the first part: On sheet "Ready" insert a new column B, and in B2 enter the formula

    =IFERROR(INDEX(C:C,MATCH(TRIM(LEFT(D2,FIND("-",D2)-1)),D:D,FALSE)),C2)

    and copy down to match your data in column C.

    Then you can use logic like this to identify master/variant/one-offs:

    =IF(COUNTIF(B:B,B2)=1,"No master",IF(B2=C2,"Master","Variant"))
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    02-07-2019
    Location
    New York
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Locate text with in text string and run "IF" commands on the row in the text located

    It worked perfectly.
    Thank you so much Bernie

+ 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: 11
    Last Post: 09-24-2017, 11:53 AM
  2. Replies: 1
    Last Post: 08-15-2014, 06:00 AM
  3. Replies: 0
    Last Post: 03-21-2014, 09:58 AM
  4. taking end text from a long string of text and "."
    By teknoboi in forum Excel General
    Replies: 3
    Last Post: 06-14-2013, 05:42 AM
  5. Replies: 10
    Last Post: 06-10-2012, 03:55 PM
  6. Replies: 4
    Last Post: 04-01-2010, 03:06 AM
  7. Replies: 3
    Last Post: 12-20-2007, 03:50 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