A User Defined Function:
Option Explicit
Function ChainKeys(ByRef KeyList As Range) As String
Dim KeyArray As Variant
KeyArray = KeyList.Value
KeyArray = WorksheetFunction.Transpose(KeyArray) 'reduce to one dimensional array
ChainKeys = Join(KeyArray, ",") 'build the string from the array elements
ChainKeys = Replace(ChainKeys, ",,", "") 'remove empty commas
ChainKeys = Replace(ChainKeys, ",", ", ") 'replace comma with comma-space
ChainKeys = Replace(ChainKeys, " ,", ",") 'remove spaces before commas
ChainKeys = IIf(Left(ChainKeys, 1) = ",", Mid(ChainKeys, 2, Len(ChainKeys)), ChainKeys) 'return the string
End Function
In cells: X103:AL103 =ChainKeys(Y3:Y102)
Copy to Results sheet (audit) with
=INDEX(Summary,1,MATCH(Employees,KeyHolders,0)
Range Name Info
|
D |
E |
F |
3 |
range name |
sheet |
address |
4 |
Employees |
results |
A3:A17 |
5 |
KeyHolders |
key log |
$X$1:$AL$1 |
6 |
Summary |
key log |
X103:AL103 |
Bookmarks