Hi
I face problem about values TOTAL from two sheets. so the code merge duplicates items based on column B for sheets REPORT1,2 and sum the values for columns QTY , TOTAL with ignore column UNIT PRICE . the column QTY sums correctly but the column TOTAL gives error values .
the result in sheet output . I put the right values in column J
Sub output()
Dim sh As Worksheet, dic As Object, rn As Range, a, n&
Set dic = CreateObject("scripting.dictionary")
For Each sh In ThisWorkbook.Sheets
If sh.Name <> "output" Then
For Each rn In Intersect(sh.[a1].CurrentRegion.Resize(, 1).Offset(, 1), sh.[a1].CurrentRegion.Offset(1))
If dic.exists(rn.Value) Then
a = dic(rn.Value)
a(1) = a(1) & "," & Right(rn(1, 2), 3)
a(6) = a(6) + rn(1, 7).Value
a(7) = a(7) + rn(1, 9).Value
If InStr(a(2) & ",", rn(1, 3).Value & ",") = 0 Then a(2) = a(2) & "," & rn(1, 3).Value
dic(rn.Value) = a
Else
ReDim a(9)
For n = 0 To 8
a(n) = rn.Offset(, n).Value
Next
a(9) = 1: a(9) = rn(1, 8)
dic(rn.Value) = a
End If
Next
End If
Next
With Sheets("output").[a2].Resize(dic.Count)
.Value = Evaluate("row(1:" & dic.Count & ")")
.Offset(, 1).Resize(, 8) = Application.Transpose(Application.Transpose(dic.items))
.Offset(, 3) = Evaluate(Replace("LEFT(#,6)&SUBSTITUTE(#,LEFT(#,6),)", "#", .Offset(, 3).Address))
End With
End Sub
I hope to find somebody help me