+ Reply to Thread
Results 1 to 9 of 9

Name (Value) in comma seperated list

  1. #1
    Registered User
    Join Date
    08-16-2020
    Location
    North America
    MS-Off Ver
    Excel 2003
    Posts
    5

    Post Name (Value) in comma seperated list

    A2:A98 is list of names
    B2:B98 is a list of values

    I have Excel 2003.

    In the sample file the table has been shortened to 25 rows

    Example Output:

    Natalie (149), Timothy (51), Amy (31), Alison (24)
    I need a comma separated list consisting of "name (value), name (value), name (value)", Where the value is greater than 20 preferably sorted from highest to lowest by the value.

    I did some basic searching and didn't find anything like what I am looking for if it's out there I apologize in advance!

    I can get the list of the names but have not been able to figure out how to add the value in parenthesis.
    Attached Files Attached Files
    Last edited by sl968; 08-16-2020 at 03:27 PM.

  2. #2
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Name (Value) in comma seperated list

    1 -What version of Excel you use?
    2 - sorted from highest to lowest by name or value
    3 - the example is preferable also.

  3. #3
    Registered User
    Join Date
    08-16-2020
    Location
    North America
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Name (Value) in comma seperated list

    Sorry this is my first time and post here. I have updated the original post with the information requested.

  4. #4
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2211 (Build 15813.20002) Win 11 Home 64 Bit
    Posts
    22,131

    Re: Name (Value) in comma seperated list

    Please update your version and general location in your profile. This will assist those willing to help you with the proper solution geared to your version of excel and your general location. While you may think this is not important, please understand that conventions within excel differ by location and versions.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Registered User
    Join Date
    08-16-2020
    Location
    North America
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Name (Value) in comma seperated list

    It is Done.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,141

    Re: Name (Value) in comma seperated list

    You can sort the data in columns A and B into the order that you require.

    Then you could use this formula in D2:

    =D1&IF(OR(A2="",B2=""),"",A2&" ("&B2&"),")

    Copy this down to the bottom of your data.

    Then to get the final csv list (without the trailing comma), you can use this in F1:

    =SUBSTITUTE(INDEX(D:D,COUNTA(D:D)+1)&"zz",",zz","")

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    08-16-2020
    Location
    North America
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Name (Value) in comma seperated list

    I came up with the full solution using parts from your reply.

    I ended up using an array function of

    Please Login or Register  to view this content.
    E is cases
    B is county names

    or using the example

    Please Login or Register  to view this content.
    A is names
    B is values

    If anyone wants to use the example paste the formula then press ctrl-shift-enter.

    Is it possible to make a one time donation to the forum. It won't be much because I am tight on money right now.
    Last edited by sl968; 08-16-2020 at 05:41 PM.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,141

    Re: Name (Value) in comma seperated list

    How can you use the TEXTJOIN function if you are using Excel 2003? It wasn't available for that version.

    Anyway, glad you found a solution. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  9. #9
    Registered User
    Join Date
    08-16-2020
    Location
    North America
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Name (Value) in comma seperated list

    My version at home must be different than the office. Either way it works! will just get them to update the office. Reputation sent!

+ 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] VBA code to create comma seperated list with one condition
    By Delta729 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-05-2018, 12:06 PM
  2. Creating a comma seperated list based on a search criteria from a column
    By HUGH JORGAN in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2013, 05:20 PM
  3. [SOLVED] Macro to change data entered in a cell seperated by a comma, into a vertical list of data
    By jholly3 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-13-2012, 03:51 PM
  4. Have 300 cells, want them in one, seperated by comma
    By AdamParker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-04-2009, 01:58 PM
  5. Comma seperated list of numbers
    By krspa9 in forum Excel General
    Replies: 1
    Last Post: 07-24-2009, 05:24 AM
  6. How to split a value seperated by comma?
    By Ticktockman in forum Excel General
    Replies: 2
    Last Post: 03-23-2006, 12:25 PM
  7. [SOLVED] Reading from comma seperated List
    By Shashi Bhosale in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2005, 03:05 PM

Tags for this Thread

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