title: Excel: Lookup All Matching Values and place in comma separated list?
body: <p>I have a Data sheet like so:</p>
  
  <pre><code>Col A    Col B    Col C    Column D   Col E   Column F     Col G    Col H      Col  I    Col J     Col K    
                             1234               Supplier 1
                             2222               Supplier 2
                             3333               Supplier 2
                             4444               Supplier 1
  </code></pre>
  
  <p>I also have another sheet</p>
  
  <pre><code>Home sheet:
  
  Column B
  Supplier 1   &lt;-- Values Produced From Index Match Formula
  Supplier 2
  </code></pre>
  
  <p>I want to list all of the item numbers from column D, Data sheet, where the supplier name on Home sheet matches.</p>
  
  <p>However, i want to place all of the matching item numbers in one cell, in a comma separated list like so:</p>
  
  <pre><code>Home sheet:    
  
  Column B         Column C
  Supplier 1       1234, 4444
  Supplier 2       2222, 3333
  </code></pre>
  
  <p>At the moment i am doing this using a user defined function in vba:</p>
  
  <pre><code>Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
  'Updateby20150824
      Dim I As Long
      Dim xRet As String
      For I = 1 To LookupRange.Columns(1).Cells.Count
          If LookupRange.Cells(I, 1) = LookupValue Then
              If xRet = "" Then
                  xRet = LookupRange.Cells(I, ColumnNumber) &amp; Char
              Else
                  xRet = xRet &amp; "" &amp; LookupRange.Cells(I, ColumnNumber) &amp; Char
              End If
          End If
      Next
      SingleCellExtract = Left(xRet, Len(xRet) - 1)
  End Function
  </code></pre>
  
  <p>And then using the following formula to get the result:</p>
  
  <pre><code>=SingleCellExtract(B14,Data!F:F,-1,",")
  </code></pre>
  
  <p>This works, however, I have more than 500 lines of data and this method takes about 10 minutes or more to calculate - and sometimes causes the sheet to crash.</p>
  
  <p>Please can someone show me a better way of doing this?</p>