+ Reply to Thread
Results 1 to 11 of 11

How to display multiple values concatenated in 1 cell without double entry of result value

  1. #1
    Registered User
    Join Date
    07-09-2012
    Location
    Riyadh Saudi
    MS-Off Ver
    Excel 2007
    Posts
    28

    How to display multiple values concatenated in 1 cell without double entry of result value

    Hi, again I'm asking everyone's help to provide the formula for below problem.

    Criteria: Look for values that matches on column A from cell F
    Result: All the values from column B will be concatenated in 1 cell after satisfying the criteria but same values at column B must not be displayed.


    See attached. Expected outputs are displayed in column G, highlighted in red.


    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094

    Re: How to display multiple values concatenated in 1 cell without double entry of result v

    Hi,

    You can do this using the Morefunc add in for excel. Download for free from

    http://longre.free.fr/english/

    Then enter the array formula below.


    =SUBSTITUTE(MCONCAT(UNIQUEVALUES(IF($A$2:$A$100=F3,", "&$B$2:$B$100,""),1)),", ","",1)

    Commit with Ctrl+Shift+Enter.

    HTH
    Steve

  3. #3
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: How to display multiple values concatenated in 1 cell without double entry of result v

    Do like this:

    1. Go here: http://www.cpearson.com/excel/stringconcatenation.aspx
    2. Copy the VBA code
    3. Press Alt+F11 to get to the VBA Window
    4. Go to menu: Insert>Module
    5. Copy and paste the code
    6. Write the Formula in your worksheet: =StringConcat(", ";IF(A3:A13=F3;B3:B13;"")) where it
    should be. Confirm the Formula by CTRL+SHIFT+ENTER, not just ENTER
    Done (I tested. It works)
    //Ola

  4. #4
    Valued Forum Contributor
    Join Date
    03-25-2004
    Location
    Boston, MA US
    Posts
    1,094

    Re: How to display multiple values concatenated in 1 cell without double entry of result v

    Hi olasa,

    I tried Chip's solution and it does concatenate based on the values in A but it does not ignore duplicates like the OP requested or maybe I missed that on Chip's site.

    Thanks,
    Steve

  5. #5
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: How to display multiple values concatenated in 1 cell without double entry of result v

    SteveG, you are right.
    Here are two alternatives, just for the fun of it.
    //Ola
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-09-2012
    Location
    Riyadh Saudi
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: How to display multiple values concatenated in 1 cell without double entry of result v

    Hi Olasa,

    Thank you for the reply. I tried to follow your instruction but when I execute the formula based on the file you send, it gives me result of #NAME?
    Kindly advise how to proceed.

  7. #7
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: How to display multiple values concatenated in 1 cell without double entry of result v

    I opened the file (in both Excel 2007 and Excel 2010) and it works straight away, since the VBA-script is al ready included in the file.
    You could try to save the file as a Macro-enabled file.
    //Ola

  8. #8
    Registered User
    Join Date
    07-09-2012
    Location
    Riyadh Saudi
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: How to display multiple values concatenated in 1 cell without double entry of result v

    I follow your instruction on another worksheet & it works unfortunately when the same concatination formula but edit only 1 column for different reference was copied, it affects all the concatinated formula & display #NAME?
    And when I repeat executing the formula, it still give back the result of #NAME. Any advise on how to solve it?

  9. #9
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: How to display multiple values concatenated in 1 cell without double entry of result v

    I am not sure I understand the problem.
    Could you attach the file with the error?
    //Ola

  10. #10
    Registered User
    Join Date
    07-09-2012
    Location
    Riyadh Saudi
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: How to display multiple values concatenated in 1 cell without double entry of result v

    Hi Olasa,

    I already figure out the cause of error. The macro setting has been disable without notification that's why it appears the error #NAME?
    Thanks for the formula, it's been very helpful but it cause to slow down the calculating process of excel. Is there any easier formula that will not cause to slow down excel?

  11. #11
    Registered User
    Join Date
    07-09-2012
    Location
    Riyadh Saudi
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: How to display multiple values concatenated in 1 cell without double entry of result v

    Dear Olasa,

    How can I concatenate values with criteria that must fall within the specified dates? I tried to use the stringconcat formula you gave and combine AND with IF to cover the criteria that must fall within the specified dates but it did not give me the expected result.
    Please help. Attach is the file. Kindly check & correct my formula.

    Thanks in advance
    Attached Files Attached Files

+ 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