• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Selecting multiple lookup values in single cell separated by comma

Vignesh832

New Member
Hi, Help me to solve multiple lookup values from comma-separated entry, and the lookup values should be in one cell using comma separation without repeating the same values. Please help me to slove this idea. Attached excel file for your view. Thank You All
 

Attachments

herofox

Member
you can to use this code
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
Else
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  Target.Value = newVal
  If Target.Column = 4 Then
    If oldVal = "" Then
          Else
      If newVal = "" Then
        Else
      Target.Value = oldVal _
        & ", " & newVal
      End If
    End If
  End If
End If
exitHandler:
  Application.EnableEvents = True
End Sub
 

Attachments

Last edited by a moderator:

bosco_yip

Excel Ninja
63305

This formula solution required OFFICE 365 for the using of TEXTJOIN function

In D2 array formula (CRTL+SHIFT+ENTER) copied down :

=TEXTJOIN(", ",1,IF(ISNUMBER(MATCH(A$2:A$6,FILTERXML("<a><b>"&SUBSTITUTE(C2,", ","</b><b>")&"</b></a>","//b"),0)),B$2:$B$6,""))

Edit: please remove trailing space at cell A2 and A5 in the OP attached file of Post #.01

Regards
Bosco
 
Last edited:

Peter Bartholomew

Well-Known Member
An alternative formula-based solution ...
First I introduced names: TextList; ValueList; CSV where the last is a relative reference to the strings.
To avoid Ctrl+Shift+Enter I defined a named formula numbers to refer to
= IF( ISNUMBER( SEARCH( TextList, CSV ) ), ValueList, "" )
This returns an array of values which is concatenated using the worksheet formula
= TEXTJOIN( ", ", TRUE, numbers )
 

Attachments

Top