Formula to Record matches and separate with commas

[Brew]

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

 

[Brew]

Any solutions?

 

[hsk]

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

If this is what u need

	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]

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]

Hi,

how about a VBA solution:

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
	DF	DG	DH	DI
7	1	3	5	1,5
8	1	2	5
Sheet1

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

 

[Peter_SSs]

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
	DE	DF	DG	DH	DI	DJ
6
7		1	5	6	1,5,6
8		1	5	6
9
10		5	8	9	5,9
11		5	4	9
12
13		7	7	3	7
14		2	7	5
15
16		2	3	4
17		5	6	7
18
19		2	7	4	7,4
20		3	7	4
21
Match Digits

 

[Brew]

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