BEG FOR HELP-EXCEL VBA CODE/FORMULA FOR A LIST

[Michael111]

test-help.xlsm

Hello guys
i really need your help
i don't know much about vba and i'm kindly asking for your help to create a list as the one shown in the table i attached.
i wish you can help me create a button to calculate, sort and filter the list i attached.
thanks so much !!!

[Jan Karel Pieterse]

The left part is easily done using a pivot table, the part where you are concatenating the L1,2,3 values is not possible with a pivottable.

[Michael111]

Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer
Dim Mat As Long
Dim MD As String
Dim Qty As Long
Dim DH As String
Dim n As Integer


n = Cells(3, 7).Value






For i = 3 To n
For j = i + 1 To n
If Cells(i, 1).Value > Cells(j, 1).Value Then
Mat = Cells(i, 1).Value
MD = Cells(i, 2).Value
Qty = Cells(i, 3).Value
DH = Cells(i, 4).Value
Cells(i, 1).Value = Cells(j, 1).Value
Cells(i, 2).Value = Cells(j, 2).Value
Cells(i, 3).Value = Cells(j, 3).Value
Cells(i, 4).Value = Cells(j, 4).Value
Cells(j, 1).Value = Mat
Cells(j, 2).Value = MD
Cells(j, 3).Value = Qty
Cells(j, 4).Value = DH
End If
Next j
Next i






For i = 3 To n
For j = i + 1 To n
If Cells(i, 1).Value = Cells(j, 1).Value And Cells(i, 1).Value <> "" Then


Cells(i, 3).Value = Cells(i, 3).Value + Cells(j, 3).Value
Cells(i, 4).Value = Cells(i, 4).Value & "" & Cells(j, 4).Value




Cells(j, 1).Value = ""
Cells(j, 2).Value = ""
Cells(j, 3).Value = ""
Cells(j, 4).Value = ""
End If
Next j
Next i






Dim lastrow As Long
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Range("A3" & lastrow).Sort key1:=Range("B3:B" & lastrow), _
order1:=xlDescending, Header:=xlNo






End Sub

[Michael111]

somebody helped me. i also changed the number of columns.