good morning !
sorry Guys I come back again for the same file .
actually I try making to merge duplicates data for each sheet separately not merge for all of the sheets .
so when merge duplicate sheet REPORT should show the result in sheet RESULT, also merge duplicate sheet REPORT2 should show the result in sheet RESULT1,
should merge duplicates data for each sheet individually
I put the result in sheet RESULT= sheet REPORT1, RESULT1=REPORT2 . see the bold lines in the code what I try it .
Sub output()
Dim sh As Worksheet, dic As Object, rn As Range, a, n&
Dim m As Long
Set dic = CreateObject("scripting.dictionary")
For Each sh In ThisWorkbook.Sheets
If (Not UCase$(sh.Name) Like "RESULT*") * (sh.[a1] = "DATE") Then
a = sh.Cells(1).CurrentRegion.Value: a(1, 1) = "ITEM"
'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(6) is stored QTY, rn(1, 7) collects QTY from sh
a(7) = a(7) + rn(1, 9).Value 'a(7) is stored TOTAL PRICE, rn(1, 9) collects TOTAL PRICE from sh
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(8) ' only needs 8
For n = 0 To 8
a(n) = rn.Offset(, n).Value
'a(6) gets QTY; 'a(7) gets UNIT PRICE ;a(8) gets TOTAL PRICE
Next
'overwrite a(7) to store TOTAL PRICE (and for output)
a(7) = a(8)
'a(9) = 1: a(9) = rn(1, 8)
dic(rn.Value) = a
End If
Next
End If
Next
m = m + 1
If Not Evaluate("isref('Result" & m & "'!a1)") Then
Sheets.Add(, Sheets(Sheets.Count)).Name = "RESULT" & m
End If
With Sheets("Result" & m).Cells(A2).Resize(dic.Count)
' 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