Get unique items and sum total on userform

[YasserKhalil]

Hello everyone
I have a textbox and listbox on userform and I need to populate the data in range A2:D17 but in specific way
As for each item would be populated once and of course to get the total of quantities and total of prices for that item
For example : "Item1" would be in listbox like that
Item1 7 200

7 is the total of quantities and 200 total of prices
Thanks advanced for help

[SamT]

A to D is 4 columns. You only have three columns in the ListBox

Something like:

Private Sub CommandButton1_Click()
With Me.ListBox1
Sheet1.Range("A2").Resize(.ListCount, .ColumnCount) = .List
End With
End Sub

[MickG]

Try this:-

Private Sub UserForm_Initialize()
Dim Rng As Range, Dn As Range, Q As Variant, Dic As Object
Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
For Each Dn In Rng
    If Not Dic.Exists(Dn.Value) Then
        Dic.Add Dn.Value, Array(Dn.Value, Dn.Offset(, 1).Value, Dn.Offset(, 2).Value)
    Else
        Q = Dic(Dn.Value)
            Q(1) = Q(1) + Dn.Offset(, 1).Value
            Q(2) = Q(2) + Dn.Offset(, 2).Value
        Dic(Dn.Value) = Q
    End If
Next
With Me.ListBox1
    .ColumnCount = 3
    .List = Application.Index(Dic.items, 0, 0)
End With
End Sub

[YasserKhalil]

Thanks a lot Mr. Sam
Thank you very much Mr. MickG. That's exactly what I was searching for
You're awesome

[MickG]

You're welcome

[YasserKhalil]

I have studied this wonderful code and nearly I got most of it except the line of .List

Can you explain this line for me please?

.List = Application.Index(Dic.items, 0, 0)