F

#### fi.or.jp.de

I have sheet1 with below data.

Col A Col B

123 AB

123 AC

123 AD

1234 AE

125 AF

145 AG

Col E should be my output.

Col D Col E

123 AB,AC,AD,AE

125 AF

145 AG

I was using the formula in cell E1 and copied across. ( array

function )

=INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=$D1,ROW($A$1:$A$6)),COLUMN(A:A)),

0)

I tried VBA

Sub test()

r1 = Cells(Rows.Count, "A").End(xlUp).Row

r2 = Cells(Rows.Count, "D").End(xlUp).Row

For a = 1 To r2

For i = 1 To r1

If Cells(a, "D") = Cells(i, "A") Then

res = Cells(i, "B")

temp = temp & "," & res

End If

Next i

With Application.WorksheetFunction

Cells(a, "E").Value = .Substitute(temp, ",", "", 1)

End With

temp = ""

Next a

End Sub

While running macro I got output in Col E as follows

Col D Col E

123 AB,AC,AD

125 AF

145 AG

Formula and VBA both are doing "exact" match but I want both close and

exact match.

Eg., ABC 123 DEF 456 is my data and I have another data as 123 DEF

In this case it should consider as matched.

Any VBA soultions ?

Thanks in advance