+ Reply to Thread
Results 1 to 16 of 16

Comma separated list of values help needed

  1. #1
    Registered User
    Join Date
    04-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    11

    Comma separated list of values help needed

    Hi

    I have a spreadsheet full of product SKU's. The sku is the style of shoe and tacked on the end is -n where n is the size of shoe.
    I'd like to have a new column that shows all of the shoe sizes available for a particular shoe style in a comma separated list but I have no idea how to do this. There are lots of shoe styles and sizes to choose from.

    My spreadsheet is formatted so that the shoe style is listed above the available shoe sizes. The attached image helps to explain what I am after.shoes.png

    Thanks

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Comma separated list of values help needed

    Can I ask why you would want to do this?

    Looking at your screencap, it appears that the data is already grouped by SKU in columnar format.

    Creating a comma delimited list will end up being one super-wide column that you might have difficulty seeing on the screen.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    04-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Comma separated list of values help needed

    Hi

    The data once complete is to be imported into a Magento shop. The software requires that I provide a comma separated list of SKU's that tell Magento which shoe sizes should be associated to a shoe style.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Comma separated list of values help needed

    OK, we can do this using a VBA user defined function.

    Are you familiar with using macros and/or VBA code?

  5. #5
    Registered User
    Join Date
    04-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Comma separated list of values help needed

    Not at all but I can follow direction.
    Here is a link to my work in progress website which shows why I want to be able to do this.
    http://www.intrigueboutique.co.uk/ha...w-reactor.html

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Comma separated list of values help needed

    Try this...

    With your file open...

    Press the key combination of Alt+F11 to open the Visual Basic Editor

    Goto the menu Insert > Module

    Copy the VBA code at the link below and paste it into the window that opens on the right side of the screen.

    http://www.excelforum.com/showthread...=1#post3096647

    Press the key combination of ALT+Q to close the Visual Basic Editor and return to Excel.

    Create a list of the unique SKU styles in column C like below:

    Data Range
    A
    B
    C
    D
    1
    SKU
    SKU
    2
    A-1
    A
    A-1,A-2,A-3
    3
    A-2
    B
    B-1,B-2,B-3,B-4,B-5
    4
    A-3
    C
    C-1,C-2
    5
    B-1
    6
    B-2
    7
    B-3
    8
    B-4
    9
    B-5
    10
    C-1
    11
    C-2
    12
    ------
    ------
    ------
    ------


    Then, enter this array formula** in D2:

    =concatall(IF(LEFT(A$2:A$11,LEN(C2))=C2,A$2:A$11,""),",")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down as needed.

  7. #7
    Registered User
    Join Date
    04-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Comma separated list of values help needed

    Almost there. Just 2 minor issues

    The first SKU which does not have a size does not need to appear on the comma separated list
    Not all of the sizes are appearing on the list

    Link to spreadsheet www.handcoded.co.uk/handcoded.xlsm

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Comma separated list of values help needed

    The reason it wasn't returning all the sizes is because you didn't adjust the range size to include all the data.

    Here's the amended array formula** with the range size changed and a tweak to exclude the first SKU style:

    =concatall(IF(LEFT(A$2:A$4970,LEN(C2))=C2,IF(LEN(A$2:A$4970)>LEN(C2),A$2:A$4970,""),""),",")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    After seeing how much data you have you might want to do this with a regular macro instead of the UDF.

    I'm not much of a programmer so I wouldn't be able to help with that.

  9. #9
    Registered User
    Join Date
    04-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Comma separated list of values help needed

    Thanks for your help however when modifying the function all I get is FALSE.
    If possible could you upload a modified version of my spreadsheet so I can see what I have done wrong.

    Thanks

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Comma separated list of values help needed

    Here ya go...

    handcoded(1).xlsm

  11. #11
    Registered User
    Join Date
    04-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Comma separated list of values help needed

    Perfect!!!

    Thanks ever so much. You have made my day. This forum is great.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Comma separated list of values help needed

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

  13. #13
    Registered User
    Join Date
    04-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Comma separated list of values help needed

    Will do.

    I have however found that the formula breaks at row 345. Row just says #VALUE!
    Are you able to pinpoint the problem?

    Thanks

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Comma separated list of values help needed

    That's because the style SKUs run out at C344.

    Try this tweak:

    =IF(C2="","",concatall(IF(LEFT(A$2:A$4970,LEN(C2))=C2,IF(LEN(A$2:A$4970)>LEN(C2),A$2:A$4970,""),""),","))

    Still array entered!

  15. #15
    Registered User
    Join Date
    04-07-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Comma separated list of values help needed

    Sorry Tony I was just being thick. I don't need the extra information past C344 at all and have what I need.
    I'll mark this thread closed.

    Thanks again

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Comma separated list of values help needed

    You're welcome!

+ 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 comma separated values in a cell to a list
    By SMB in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-03-2019, 01:10 AM
  2. [SOLVED] Single cell Comma Separated Values to validation list [DIFFICULT QUESTION]
    By jgema in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-31-2013, 08:48 PM
  3. Validate comma-separated list of values
    By DeDomme in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-21-2013, 12:27 PM
  4. [SOLVED] Extracting information from a comma separated list of values in one cell
    By cardiff in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-11-2012, 09:17 PM
  5. Comma separated values
    By shrikantk in forum Excel General
    Replies: 3
    Last Post: 05-18-2009, 09:52 AM

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