Hi All,

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