First enter the following User Defined Function in a standard module:
Public Function Konnect(r1 As Range, r2 As Range) As String
Dim ary, i As Long
Dim v As Variant
ary = r2
v = r1.Value
Konnect = ""
For i = LBound(ary, 1) To UBound(ary, 1)
If ary(i, 1) = v Then Konnect = Konnect & "," & ary(i, 2)
If Len(Konnect) <> 0 Then Konnect = Mid(Konnect, 2)
Then in Sheet1 cell B1 enter:
and copy down:
Naturally, you would change the 10 to match the actual table size on Sheet2.
User Defined Functions (UDFs) are very easy to install and use:
- ALT-F11 brings up the VBE window
ALT-M opens a fresh module
- paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx
To remove the UDF:
- bring up the VBE window as above
- clear the code out
- close the VBE window
To use the UDF from Excel:
To learn more about macros in general, see:
and for specifics on UDFs, see:
Macros must be enabled for this to work!