Select from Dropdown List and Concatenate Chosen Values Separated by Comma

[nerdy_nerdsalot]

Hi all, I have created a table of names on Sheet 1 called 'List'. On Sheet 2, I have created columns 'Task' and 'List'. I would like to choose multiple names in the list column drop-down and have them concatenated separated by commas in the same cell or an adjacent cell. Is this possible? How can this be done? I'm open to any solutions, preferably the simplest, including basic Excel functions or VBA scripts. Also, I'm open to changing the general structure of the sheet if what I'm asking for can be done in an alternative way

I'm using Data Validation for the drop-down list, however this only allows me to choose one name from the list at a time (unless I were to create another value with the names already concatenated which would defeat the purpose!)

Thank you,

Nerdy Nerdsalot

 

[mr2bart]

Hi,
by drop-down list, only way is what you did: already concatenated datas.
From my opinion, it's the best and to concatenated, there maybe you need VBA depending your datas.

 

[nerdy_nerdsalot]

Hi,
by drop-down list, only way is what you did: already concatenated datas.
From my opinion, it's the best and to concatenated, there maybe you need VBA depending your datas.

Thanks mr2bart. To give a more specific example with data that captures the essence of what I'm looking for, please see below:

[TABLE="width: 130"]
<colgroup><col></colgroup><tbody>[TR]
[TD]List[/TD]
[/TR]
[TR]
[TD]Albert Apple[/TD]
[/TR]
[TR]
[TD]Billy Bob[/TD]
[/TR]
[TR]
[TD]Cathy Catherston[/TD]
[/TR]
[TR]
[TD]David Daniels[/TD]
[/TR]
[TR]
[TD]Esther Estelle
[/TD]
[/TR]
</tbody>[/TABLE]

Task below begins in cell C4

[TABLE="width: 971"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Task
[/TD]
[TD]List[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]Buy lightbulb[/TD]
[TD]Albert Apple[/TD]
[TD]D5 needs to be "Albert Apple, Billy Bob" by choosing multiple drop-down list items[/TD]
[/TR]
[TR]
[TD]Wait for lightbulb[/TD]
[TD]Cathy Catherston[/TD]
[TD]D6 needs to be "Cathy Catherston, David Daniels, Esther Estelle" by choosing multiple drop-down list items[/TD]
[/TR]
[TR]
[TD]Screw in lightbulb[/TD]
[TD]Billy Bob[/TD]
[TD]D7 needs to be "Billy Bob, Esther Estelle" by choosing multiple drop-down list items[/TD]
[/TR]
[TR]
[TD]Turn on lightbulb[/TD]
[TD]Cathy Catherston[/TD]
[TD]D8 needs to be "David Daniels, Cathy Catherston" in that order by choosing multiple drop-down list items
[/TD]
[/TR]
[TR]
[TD]Party[/TD]
[TD]Albert Apple[/TD]
[TD]D9 needs to be everyone separated by commas by choosing multiple drop-down list items[/TD]
[/TR]
</tbody>[/TABLE]

Thanks and best regards,

Nerdy Nerdsalot

 

[TrumpExcel]

You can use the following VAB code to do this:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim Oldvalue As String
Dim Newvalue As String


On Error GoTo Exitsub


If Target.Address = "$C$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else


Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If


Application.EnableEvents = True


Exitsub:
Application.EnableEvents = True


End Sub

You would need to put in the cell address of the cells that have the drop down list in it. I have used C3 in this code.

Here is the download file --> Download

This is a worksheet change event so it would go in the code area for the worksheet.
You can read more about it here --> Select Multiple Items from a drop down list