F
fi.or.jp.de
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
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