Extracting Unique values, Sorting And summing

[amit_gupta123]

Hii

Below are the two screenshots one is Input and other is output

Input Screen


Output Screen



Can you please tell me how to extract unique value from column D and sort it and then sumif column B And Column C

Regards

 

[Hui]

How about using a Pivot Table?

Select A1:D11
Goto Insert, Pivot table
Follow Prompts
Configure as

 

[amit_gupta123]

Thanks for the response...I do this

But appreciate if it could be done through VbA

 

[jindon]

If this doesn't work, attach your workbook.

Sub test()
    Dim a, i As Long, w, x As Object
    a = Cells(1).CurrentRegion.Value
    With CreateObject("System.Collections.SortedList")
        For i = 2 To UBound(a, 1)
            If Not .Contains(a(i, 4)) Then
                .Item(a(i, 4)) = Array(a(i, 4), a(i, 2), a(i, 3))
            Else
                w = .Item(a(i, 4))
                w(1) = w(1) + a(i, 2): w(2) = w(2) + a(i, 3)
                .Item(a(i, 4)) = w
            End If
        Next
        Set x = .Clone
    End With
    With Sheets.Add.Cells(1).Resize(, 3)
        .Value = Application.Index(a, 1, [{4,2,3}])
        For i = 0 To x.Count - 1
            .Rows(i + 2).Value = x.GetByIndex(i)
        Next
    End With
End Sub

 

[Hui]

Jindon

I really like parts of that code, well done

 

[Visor]

Is very genial,
but I dont know,,.. whats about the items, this is confuse for me
Of course, that's how you want the result

 

[amit_gupta123]

Excellent ...it worked.....

 

[Marc L]

Hi !

Other way for beginners : use Excel BASICS !

Like advanced filter, sort and easy COUNTIF worksheet function …
​

 

[jindon]

Jindon

I really like parts of that code, well done

Thanks.