The below formula will remove duplicates based on column A and B it will also apend column D with the values before removing the duplicate.
For the following statement, am I correct in thinking i,1 is referring to column A and i, 2 to Column B . so If I want to have it join with column A and C I just increment the i,2 to i,3??
What is the Chr(2)
( txt = Join$(Array(a(i, 1), a(i, 2)), Chr(2))
for this statement would I just change all 3 of the 4's to 5's if I needed to apend column E vice D?
a(.Item(txt), 4) = a(.Item(txt), 4) & "," & a(i, 4)
Sub Impact_Assesment()
Sheets("Impact Assesment").Select
Dim a, i As Long, ii As Long, n As Long, txt As String
a = Sheets("Consolidated").Range("a1").CurrentRegion
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 1 To UBound(a, 1)
txt = Join$(Array(a(i, 1), a(i, 2)), Chr(2))
If Not .exists(txt) Then
n = n + 1: .Item(txt) = n
For ii = 1 To UBound(a, 2)
a(n, ii) = a(i, ii)
Next
Else
End If
Next
End With
With Sheets("Impact Assesment").Range("b1").Resize(n, UBound(a, 2))
.CurrentRegion.ClearContents
.Value = a
.Columns.AutoFit
End With
Bookmarks