Formula to Record matches and separate with commas

Brew

Well-known Member
Joined
Sep 29, 2003
Messages
1,569
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
 

Some videos you may like

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.

hsk

Well-known Member
Joined
Oct 19, 2006
Messages
567
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
Joined
Sep 29, 2003
Messages
1,569
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
Joined
Jul 18, 2003
Messages
4,494

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
46,871
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 29, 2003
Messages
1,569
Thanks guys for the solutions...Peter and al_b_cnu, I think I will go with the formula solution rather than the code.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,095
Messages
5,545,923
Members
410,713
Latest member
TaremyLunsil
Top