Hey

Have you tried using the Instr Function in vba
If instr(string,1'string2)0 then

I think it will be better written in a function not a sub so its more
flexible adjusting the data range
Let me know with you need help writing the Function
What i figured its the same thing as a multiple vlookup

Cheers


" wrote:

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