# Formula to Record matches and separate with commas

#### Brew

##### Well-known Member
How do I create a formula that will record the value(s) from DF8:DH8 that match the values in DF7:DH7 and separate multiple match results by commas in the same cell

example1

if df8:dh8=124 and
if df7:dh7=125 then
di7=1,2 (because df8=df7 and dg8=dg7

example2

if df8:dh8=125 and
if df7:dh7=135 then
di7=1,5 (because df8=df7 and dh8=dh7

example3

if df8:dh8=115 and
if df7:dh7=134 then
di7=1 (because df8=df7

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Any solutions?

#### hsk

##### Well-known Member
I don't know if this will suit ur requirement but see the solution i cud think of,

If this is what u need

Code:
``````	DF	DG DH	DI
7	1	3	5
8	1	2	5
9	1,	   5,	1, 5,``````

Cell DI9 gives the result.

I put formulae in cells DF9, DG9,DH9 and DI9 as
Cell DF9 will have formula .....
=IF(DF14=DF15,DF15 & ","," ")

Copy same to DG and DH column (excel will automatically change DF to corrosponding columns)

Then DI9 will have formula
=CONCATENATE(DF16,DG16,DH16)
[/img]

#### Brew

##### Well-known Member
Interesting solution, however, I would rather have one formula in DI9
that compares DF8:DH8 with DF7:DH7 to find the matching values and
record the matches

#### al_b_cnu

##### Well-known Member

Hi,

how about a VBA solution:
Code:
``````Function Matches(ByVal Range1 As Range, ByVal Range2 As Range) As String
Dim iPtr As Integer, iLen As Integer
Dim iOffCol As Integer
Dim lOffRow As Long
Dim R As Range

Dim sResult As String
iOffCol = Range2.Column - Range1.Column
lOffRow = Range2.Row - Range1.Row

For Each R In Range1
If R.Text = R.Offset(lOffRow, iOffCol).Text Then sResult = sResult & "," & R.Text
Next R

iLen = Len(sResult)
If iLen > 1 Then
sResult = Right\$(sResult, iLen - 1)
Else
sResult = ""
End If
Matches = sResult

End Function``````

Example:
Book1
DFDGDHDI
71351,5
8125
Sheet1

Formula in DI7 is =matches(DF7:DH7,DF8:DH8)

#### Peter_SSs

##### MrExcel MVP, Moderator
Brew

Using standard formulas, try this. Formula in DI7 (copied to DI10, DI13 etc) is:
=SUBSTITUTE(SUBSTITUTE(IF(DF7=DF8,DF7," ")&","&IF(DG7=DG8,DG7," ")&","&IF(DH7=DH8,DH7," "),", ","")," ,","")
Mr Excel.xls
DEDFDGDHDIDJ
6
71561,5,6
8156
9
105895,9
11549
12
137737
14275
15
16234
17567
18
192747,4
20374
21
Match Digits

#### Brew

##### Well-known Member
Thanks guys for the solutions...Peter and al_b_cnu, I think I will go with the formula solution rather than the code.

Replies
4
Views
330
Replies
10
Views
306
Replies
2
Views
224
Replies
9
Views
376
Replies
2
Views
597