+ Reply to Thread
Results 1 to 5 of 5

How can I change duplicate values in a column based on the value in an another column?

  1. #1
    Registered User
    Join Date
    07-30-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    How can I change duplicate values in a column based on the value in an another column?

    I work for a clothing retailer. We generate a unique SKU code for each style on the basis of its attributes.

    I have been able to use VLOOKUP's to generate an attribute code based on the values selected in a series of drop down lists, but now need to sequentially number any styles with identical attribute codes to create a unique SKU code for each style

    For example, I may have several dresses which have identical attributes, but each one must have a unique SKU code because they are different styles.

    This is made more complicated because we have several sizes of each style which must have the same SKU, so only need the sequential number to change at each change in the supplier code. Styles with identical attribute codes will not always appear together in the sheet.

    Please see attached for a very basic example of the raw data and the unique SKU numbers which I have generated manually. The final result must be formatted as the SKU column in my example for our systems, but it would be fine to add another column which contains the sequential number and then use an & formula to create the final code.

    Have been racking my brain to try and work out a scalable formula/function/macro which would automate this, hope someone can help!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: How can I change duplicate values in a column based on the value in an another column?

    I have included a sample workbook. I have divided the input from the output. It uses DATA VALIDATION for pull down lists and VLOOKUP for the data output. I added 3 "Helper" columns

    EXAMPLE2.xlsx
    Click on star (*) below if this helps

  3. #3
    Registered User
    Join Date
    07-30-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: How can I change duplicate values in a column based on the value in an another column?

    Thanks so much for the quick reply!

    The first attachment was a basic example, but the actual document is more complex (attached)

    The style codes and colors come from our suppliers and will change for every brand and season so this cannot be a pre-defined list.

    Each digit of the attribute section of the SKU is generated from VLOOKUP's against variable drop downs, with additional IF formulas supporting other functions we need the SKU to fulfill, so there would be too many possible combinations to split the input and output in this way

    Once the attribute section of the SKU has been generated by the existing formulas, I need excel to recognise duplicates in the resulting values, but only add the sequential number at each change in the unique style code to avoid create individual SKU's for each size

    Not sure I'm explaining this very clearly, but hope you can help!
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: How can I change duplicate values in a column based on the value in an another column?

    I can't follow your example. Perhaps others can help.
    ========================
    PS: Why aren't at the Olympics instead of being indoors?

  5. #5
    Registered User
    Join Date
    07-30-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: How can I change duplicate values in a column based on the value in an another column?

    Thanks anyway, have a great evening!

+ 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