Sorting based on one column values and removing duplicates!

Originally Posted by rcm

Please Login or Register  to view this content.

This code is working for the Col's C and D but not for the week range column (Col E)...its not getting separated by commas.
For me the week range(Col E) sorting alone is OK...i want to find out how many week ranges are there for a particular manager.

Hi devpp,
Try this code :

Please Login or Register  to view this content.

Try this :

PHP Code: 

Sub ExtractData()
Dim i As Long, k As Long, j As Long, jJ As Long
Dim sArr(), dArr(), Dic
sArr = Range("A2:F15").Value
ReDim dArr(1 To UBound(sArr, 1), 1 To 6)
Set Dic = CreateObject("scripting.dictionary")
For i = 1 To UBound(sArr, 1)
With Dic
If Not .Exists(sArr(i, 1)) Then
    k = k + 1
    .Add sArr(i, 1), k
        For j = 1 To 6
            dArr(k, j) = sArr(i, j)
        Next j
    Else
    jJ = .Item(sArr(i, 1))
    dArr(jJ, 3) = dArr(jJ, 3) & "," & sArr(i, 3)
    dArr(jJ, 4) = dArr(jJ, 4) & "," & sArr(i, 4)
End If
End With
Next
[A19:F50].ClearContents
If k Then [A19].Resize(k, 6).Value = dArr
End Sub