Hi All,
VBA Code, Want to concatenate the values based on the conditions met,
Input:
A B
1 1
2 2
3 2
4 2
5 3
6 4
7 4
8 5
in the above input, values in B2,B3,B4 and B6,B7 are same, so values in A3,A4 needs to concatenate with the value in A2.
also values in A7 needs to concatenate with the value in A6.
Once concatenated, rows needs to be deleted and please look below output for better understanding
Output:
A B
1 1
2,3,4 2
5 3
6,7 4
8 5
Re: Concatenate the values, based on the conditions matched
Hi All,
i got the below code, its working fine:smile:
Option Explicit
Sub Concatenate()
Dim LastRow As Long
Dim I As Long, J As Long
Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
I = 2
Do
J = I + 1
Do
If Cells(J, "B") = Cells(I, "B") Then
Cells(I, "A") = Cells(I, "A") & "," & Cells(J, "A")
Rows(J).Delete
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Else
J = J + 1
End If
Loop While (J <= LastRow)
I = I + 1
Loop While (I <= LastRow)
Application.ScreenUpdating = True
End SubRe: Concatenate the values, based on the conditions matched
Here's a little bit simpler solution:
Sub Test()
Dim LRow As Integer, intLp As Integer
LRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For intLp = LRow To 1 Step -1
If intLp > 1 Then
If Cells(intLp, 2) = Cells(intLp - 1, 2) Then
Cells(intLp - 1, 1) = Cells(intLp - 1, 1) & ", " & Cells(intLp, 1)
Cells(intLp, 1).EntireRow.Delete
End If
End If
Next intLp
End SubRe: Concatenate the values, based on the conditions matched
[FONT=Tahoma, Calibri, Verdana, Geneva, sans-serif]Thank you @[/FONT]mrmmickle1
Don’t have an account yet? Register yourself now and be a part of our community!