How to display multiple values in a single cell in Excel

I'm am relatively familiar with Excel and its functions, but am very new to VBA (I do however have background in MATLAB and C). Basically what I have is a sheet with a different machine populating each column header and a name of an employee populating the first column. The table contains text values of either "Train", indicating that the person in that row is trained on the equipment in the specified column, or "No", indicating that they are not. What I want to do is to make a separate sheet that has the Equipment in the first column and one column headered as "Trained". Each cell will theoretically be populated with the names of the people who are trained on the equipment for that row. I have a for loop code in VBA that successfully outputs the names into the immediate window

Function Train(Data As Range, Name As Range)

    For Counter = 1 To Data.Rows.Count

        If Data(Counter, 1).Value = "Train" Then
            'Debug.Print Name(Counter, 1)
        End If

    Next Counter

End Function

but I have been unable in a few hours of searching to figure out how to display these values in a single cell. Is this possible?

Tha开发者_运维技巧nks in advance!

You have to choose if you want to do "For each person, for each machine", or "for each machine, for each person" first. Let say you want to go with the second idea, you could go with this pseudo code:

set wsEmployee = Worksheets("EmployeeSheet")
set wsEmployee = Worksheets("MachineSheet")
'Clear MachineSheet and add headers here

xEmployee = 2
yMachine = 2

do while (wsEmployee.Cells(1, xEmployee).Value <> "") 'or your loop way here
    yEmployee = 2
    trained = ""
    do while (wsEmployee.Cells(yEmployee, 1).Value <> "") 'or your loop way here
        if (wsEmployee.Cells(yEmployee, xEmployee).Value = "Trained") then
            trained = trained & wsEmployee.Cells(yEmployee, 1).Value & ", "
        end if
        yEmployee = yEmployee + 1
    loop
    'remove the last , in the trained string
    wsMachine.Cells(yMachine, 1).Value = wsEmployee.Cells(1, xEmployee).Value
    wsMachine.Cells(yMachine, 2).Value = trained
    yMachine = yMachine + 1
    xEmployee = xEmployee + 1
loop

That's the basic idea. For better performances, I would do all these operation in some arrays and paste them in one operation.

Use the concatenation operator (&) to assemble the values into a string:

Dim names as String
names = ""
For Counter = 1 To Data.Rows.Count

    If Data(Counter, 1).Value = "Train" Then
       If counter = 1 Then
           names = names & Name(counter, 1)
       Else
           names = names & "," & Name(counter, 1)
       End If
    End If

Next Counter

Then just place names in whatever cell you want.