Single cell Comma Separated Values to validation list [DIFFICULT QUESTION]

To Attach a Workbook:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit

When the list is created by vba it is possible:

Sub Macro1()
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Range("D4").Value
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

with the list created in A1 and the values in 1 cell comma separated in D4

Originally Posted by tehneXus

When the list is created by vba it is possible:

Sub Macro1()
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Range("D4").Value
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

with the list created in A1 and the values in 1 cell comma separated in D4

The VBA function I use is the following.

Option Explicit

Public Function FindProd(TRange As Range, MatchWith As String)

Dim cell As Variant
Dim xString As Variant

    For Each cell In TRange
        If cell.Value = MatchWith Then
                        
            xString = xString & cell.Offset(0, 1).Value & ","
        End If
    Next cell
     
     
    xString = Left(xString, (Len(xString) - 1))
     
    
'Checking for dups

    Dim UniqueValues As New Collection
    Dim x As Variant, Item As Variant
    Dim Txt As Variant, ConcatValues As Variant
    Dim i As Long
    
    Txt = xString
    
    'Txt = Application.Substitute((Range("A2").Value), " ", "")

    x = Split(Txt, ",")
    
    On Error Resume Next
    For i = LBound(x) To UBound(x)
        UniqueValues.Add x(i), CStr(x(i))
    Next i
    On Error GoTo 0
    
    For Each Item In UniqueValues
        ConcatValues = ConcatValues & "," & Item
    Next Item
    
    ConcatValues = Mid(ConcatValues, 2)
    
    xString = ConcatValues

    FindProd = xString

 
End Function

Of course, it isn't "clean" but it gets the job done for finding values of table then deleting duplicates.

Is it possible to alter it so it also allows me to create a validation list dynamically in the same row that the function is used? Example: If the function is used in B5, I want a validation list with the found data to be shown in C5.

Thank you!!!!!

seeme to me that you are making things unnecessarily difficult for yourself? If you have VBA to extract all producers of a particular wine, from a list, why not just nake a list of the producers, instead of combining them - and then having to break them apart again later?

Originally Posted by FDibbins

seeme to me that you are making things unnecessarily difficult for yourself? If you have VBA to extract all producers of a particular wine, from a list, why not just nake a list of the producers, instead of combining them - and then having to break them apart again later?

I can make a list, but the issue is that I don't know how to take it and put it in a drop-down afterwards without having to output an array with the function. I'm not very good with macros (never learnt them); I use functions. With the comma separated values, I thought that by simply selecting the cell, the validation list would cascade the results but instead, it just shows the string that is found in the cell (ex: a,b is shown as a,b, instead of a 2 choices which are a and b).

How can I solve the issue without making it so complicated? The necessary that has to be done is the following

- Find the producers that produce the same name of wine and display them in a validation list afterwards

WINE NAME | Producers
(dropdown of | Display all producers
many names) | that make the wine name
When one is | selected
selected, find
all producers
that make it

Depending on how your data is laid out, that might be a very *simple* formula solution. can you provide a sample workbook, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Im sorry, my VBA is poor at best