Hi.,
The below code is from one of the thread from the forum.
the output is as follows
but i want consolidated sum of the materials ( 10 materials, no duplicates, with proper sum with respect to width, length and thick) with respect the quantities in the sheet 2. Presently the code is not giving desired output. It gives sum of qty correctly for the following higlighted cell.
The below code is from one of the thread from the forum.
VBA Code:
Sub MG03Nov36()
Dim Rng As Range, Dn As Range, n As Long, txt As String, Ac As Long
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 6)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
txt = Dn.Offset(, 2).Value & "," & Dn.Offset(, 3).Value & "," & _
Dn.Offset(, 4).Value & Dn.Offset(, 5).Value
If Not .Exists(txt) Then
n = n + 1
For Ac = 1 To 6
ray(n, Ac) = Format(Dn.Offset(, Ac - 1), "@")
Next Ac
.Add txt, n
Else
ray(.Item(txt), 2) = ray(.Item(txt), 2) + Dn.Offset(, 1)
End If
Next
End With
With Sheets("Sheet2").Range("A1").Resize(n, 6)
.Value = ray
.Borders.Weight = 2
.Columns.AutoFit
End With
End Sub
the output is as follows
Book1.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | ITEM | QTY | WIDTH | LENGTH | THICK | MATL | ||
2 | 1 | 24 | 23 | 42 | 13/16 | Solid Ash | ||
3 | 2 | 2 | 26.375 | 32.875 | 04-03-22 | Oak Ply | ||
4 | 3 | 2 | 16.75 | 32.875 | 04-03-22 | Oak Ply | ||
5 | 4 | 2 | 3 | 32.375 | 04-03-22 | Solid Ash | ||
6 | 5 | 2 | 2.75 | 32.375 | 04-03-22 | Solid Ash | ||
7 | 6 | 2 | 1.5 | 32.375 | 04-03-22 | Birch Ply | ||
8 | 7 | 4 | 1.75 | 32.375 | 04-03-22 | Birch Ply | ||
9 | 8 | 24 | 2 | 9.5 | 1.25 | Hardwood | ||
10 | 9 | 24 | 1.5 | 19.5 | 04-03-22 | Birch Ply | ||
11 | 10 | 2 | 14.5 | 29.375 | 02-01-22 | CDX Plywood | ||
12 | 2 | 2 | 26.375 | 46.875 | 04-03-22 | Oak Ply | ||
13 | 3 | 2 | 16.75 | 46.875 | 04-03-22 | Oak Ply | ||
14 | 4 | 2 | 3 | 46.375 | 04-03-22 | Solid Ash | ||
15 | 5 | 2 | 2.75 | 46.375 | 04-03-22 | Solid Ash | ||
16 | 6 | 2 | 1.5 | 46.375 | 04-03-22 | Birch Ply | ||
17 | 7 | 4 | 1.75 | 46.375 | 04-03-22 | Birch Ply | ||
18 | 10 | 2 | 14.5 | 43.375 | 02-01-22 | CDX Plywood | ||
19 | 2 | 4 | 26.375 | 52.875 | 04-03-22 | Oak Ply | ||
20 | 3 | 4 | 16.75 | 52.875 | 04-03-22 | Oak Ply | ||
21 | 4 | 4 | 3 | 52.375 | 04-03-22 | Solid Ash | ||
22 | 5 | 4 | 2.75 | 52.375 | 04-03-22 | Solid Ash | ||
23 | 6 | 4 | 1.5 | 52.375 | 04-03-22 | Birch Ply | ||
24 | 7 | 8 | 1.75 | 52.375 | 04-03-22 | Birch Ply | ||
25 | 10 | 4 | 14.5 | 49.375 | 02-01-22 | CDX Plywood | ||
26 | 2 | 4 | 26.375 | 64.875 | 04-03-22 | Oak Ply | ||
27 | 3 | 4 | 16.75 | 64.875 | 04-03-22 | Oak Ply | ||
28 | 4 | 4 | 3 | 64.375 | 04-03-22 | Solid Ash | ||
29 | 5 | 4 | 2.75 | 64.375 | 04-03-22 | Solid Ash | ||
30 | 6 | 4 | 1.5 | 64.375 | 04-03-22 | Birch Ply | ||
31 | 7 | 8 | 1.75 | 64.375 | 04-03-22 | Birch Ply | ||
32 | 10 | 4 | 14.5 | 61.375 | 02-01-22 | CDX Plywood | ||
Sheet2 |
but i want consolidated sum of the materials ( 10 materials, no duplicates, with proper sum with respect to width, length and thick) with respect the quantities in the sheet 2. Presently the code is not giving desired output. It gives sum of qty correctly for the following higlighted cell.
Book1.xlsm | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | ITEM | QTY | WIDTH | LENGTH | THICK | MATL | ||
2 | 1 | 24 | 23 | 42 | 13/16 | Solid Ash | ||
3 | 2 | 2 | 26.375 | 32.875 | 04-03-22 | Oak Ply | ||
4 | 3 | 2 | 16.75 | 32.875 | 04-03-22 | Oak Ply | ||
5 | 4 | 2 | 3 | 32.375 | 04-03-22 | Solid Ash | ||
6 | 5 | 2 | 2.75 | 32.375 | 04-03-22 | Solid Ash | ||
7 | 6 | 2 | 1.5 | 32.375 | 04-03-22 | Birch Ply | ||
8 | 7 | 4 | 1.75 | 32.375 | 04-03-22 | Birch Ply | ||
9 | 8 | 24 | 2 | 9.5 | 1.25 | Hardwood | ||
10 | 9 | 24 | 1.5 | 19.5 | 04-03-22 | Birch Ply | ||
11 | 10 | 2 | 14.5 | 29.375 | 02-01-22 | CDX Plywood | ||
Sheet2 |