# Matching a single cell to multiple cells

• 08-23-2013, 03:52 PM
jsp01
Matching a single cell to multiple cells
I am trying to match a single cell to other cells sharing the same value. In the simplified example below, the idea is to show all pet stores that also contain the specified pet. The fist table demonstrates the starting data, and the second table illustrates the desired output. In the case of the actual dataset there are hundreds of "pets" and dozens of "stores". Thanks.

Input:
 cat pet store 1 dog pet store 1 hamster pet store 1 cat pet store 2 dog pet store 2 parrot pet store 2 dog pet store 3 hamster pet store 3

Output:
 cat pet store 1 pet store 2 dog pet store 1 pet store 2, pet store 3 hamster pet store 1 pet store 3 cat pet store 2 pet store 1 dog pet store 2 pet store 1, pet store 3 parrot pet store 2 dog pet store 3 pet store 1, pet store 2 hamster pet store 3 pet store 1
• 08-23-2013, 04:20 PM
rcm
Re: Matching a single cell to multiple cells
Code:

```Sub match() On Error Resume Next Sheets("sheet1").Select r = 2 r2 = 1 While Cells(r, 1) <> "" j = 0 j = Application.WorksheetFunction.match(Cells(r, 1), Range("a1:a" & r - 1), 0) If j > 0 Then If Cells(j, 6) <> "" Then sep = "," Else sep = "" Cells(j, 6) = Cells(j, 6) & sep & Cells(r, 2) Else r2 = r2 + 1 Cells(r2, 4) = Cells(r, 1) Cells(r2, 5) = Cells(r, 2) End If r = r + 1 Wend End Sub```
• 08-23-2013, 04:22 PM
mlcb
Re: Matching a single cell to multiple cells
A possible solution is a Pivot Table
• 08-24-2013, 02:50 AM
kvsrinivasamurthy
Re: Matching a single cell to multiple cells
Pl see the attached file.