PDA

View Full Version : Get unique items and sum total on userform



YasserKhalil
03-03-2017, 12:12 AM
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
03-03-2017, 05:09 AM
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
03-03-2017, 05:10 AM
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
03-03-2017, 10:31 AM
Thanks a lot Mr. Sam
Thank you very much Mr. MickG. That's exactly what I was searching for
You're awesome

MickG
03-03-2017, 10:35 AM
You're welcome

YasserKhalil
03-03-2017, 10:36 AM
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)