VBA for Remove & Concat

[The_Rock]

Hi Folks
I hope you can help with writing vba for the following.

I have 3 columns:

Location Oppt Status
UK1 JS001 Approved
UK1 JS002 Pending
NLD1 GS001 Approved
NLD1 GS002 Rejected
NLD1 GS003 Rejected

What I would like to do is go thru the first column, to make the 'Location' unique and then concat Oppty & Status, for example, using the above, the result should be:
Location Result
UK1 JS001 (Approved), JS002 (Pending)
NLD1 GS001 (Approved), GS002 (Rejected), GS003 (Rejected)

Hope you guys can help

 

[puru.sve]

You can use this .... If you data is in A, B and c column then it will show output at column M and N .. so can adjust this program as your data positioning

Dim lstRow, countRow, uniCounter, uniLstRow As Long
Dim flag As Integer
Dim strAddress As String
With ActiveSheet

.Range("M:N").ClearContents
lstRow = .Range("a65536").End(xlUp).Row

.Range("N1").Value = "Result"
.Range("M1:M" & lstRow).Value = .Range("A1:A" & lstRow).Value

.Range("M1:M" & lstRow).RemoveDuplicates Columns:=1, Header:=xlYes
uniLstRow = .Range("M65536").End(xlUp).Row
For uniCounter = 2 To uniLstRow

For countRow = 2 To lstRow

If .Range("M" & uniCounter).Value = .Range("A" & countRow).Value Then

If .Range("N" & uniCounter).Value <> "" Then
.Range("N" & uniCounter).Value = .Range("N" & uniCounter).Value & ", "
End If
.Range("N" & uniCounter).Value = .Range("N" & uniCounter).Value & .Range("B" & countRow).Value & " (" & .Range("C" & countRow).Value & ")"
End If


Next
Next


End With

 

[The_Rock]

puru.sve - take a bow, you are a star my man!!
This is great, thank you!!