insert three columns with merge duplicate items and calculation across sheets

hi guys
I need macro to insert three columns in sheet FINISHING after matching column B with the others sheets and merge duplicate items across sheets for columns sales & returns and calculation as the formula in column BALANCE( the formula shouldn't show) with considering two things somtimes there is items are existed in the others sheets but not existed in sheet finishing should be add it into sheet finishing ,will add new sheets every time ( the same structure as in the others) also should show hyphen for empty or zero value for cells with formatting of numbers .
the result should be in columns D,E,F in sheet finshing
Last thing when run the macro repeatedly should not insert columns every times just update data if add new data or change .

Try this:

Please Login or Register  to view this content.

Try this:

Please Login or Register  to view this content.

ITEMS sheet
3 TR 1425/148V MN 123H K/L 15

FINISHING sheet (macro):
4 TR 1425/148V MN 123H K/L 1,220.00 15.00 - 1,205.00


right result (Your answer)
4 TR 1425/148V MN 123H K/L 1,220.00 30.00 4.00 1,194.00



why 30?
The ITEM "TR 1425/148V MN 123H K/L" only exists in the "items" sheet. So the macro is fine. Check your data.

In sheet2 it is not the same, since it has 2 spaces, between "V" and "MN" instead of 1, that's why they are not equal. You must review your data. The macro is fine.

ITEMS sheet
TR 1425/148V_MN 123H K/L

Sheet2
TR 1425/148V__MN 123H K/L

ok I clean up the spaces to matching but even if this you still don't understand me ( take CTR 12-200M45 MM 123H K/L as in example )
after run the macro sales gives 280 how? it's just existed in first sheet should be 30 also return gives 34 how? should be 4 also the balance how gives 4 ? should be (250-30+4=224)
I no know why you think your code works ? if I exclude the proplems the spaces otherwise the values are extremely wrong , please take your time and just see the item I gave you as example I'm so confused .
do me favor,please ?
just attach the file and will inform you how works .

HTML Code: 

Option Explicit
Sub test()
Dim lr&, i&, rng, ws As Worksheet
Dim dic As Object, key
Set dic = CreateObject("Scripting.dictionary")
For Each ws In Sheets
    If ws.Name <> "FINISHING" Then
        lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
        rng = ws.Range("B2:D" & lr).Value
        For i = 1 To lr - 1
            If Not dic.exists(rng(i, 1)) Then
              dic.Add rng(i, 1), rng(i, 2) & "|" & rng(i, 3)
            Else
            On Error Resume Next
                dic(rng(i, 1)) = (Split(dic(rng(i, 1)), "|")(0) + rng(i, 2)) & "|" & (Split(dic(rng(i, 1)), "|")(1) + rng(i, 3))
            On Error GoTo 0
            End If
        Next
    End If
Next
With Worksheets("FINISHING")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To lr
        For Each key In dic.keys
            If .Cells(i, "B").Value = key Then
                .Cells(i, "D").Value = Split(dic(key), "|")(0)
                .Cells(i, "E").Value = Split(dic(key), "|")(1)
                .Cells(i, "F").Value = .Cells(i, "C").Value + .Cells(i, "E").Value - .Cells(i, "D").Value
            End If
        Next
    Next
End With
End Sub

@bebo021999 thanks
first
also there is new item into one of sheets but not existed in sheet finshing should be add it and dd hyphen in column C .
add numberformat foe each value like this "#,##0.00" .
should show hyphen for empty or zero value for cells with formatting of numbers .
now it doesn't give right result . see the post#9 and compare sheet finishing with last sheet
your code gives the same result as DanteAmore's code.
may you attach the file maybe the problem from my file ,please?

Here it is. Pls highlight where the issue is.

Again:

HTML Code: 

Option Explicit
Sub test()
Dim lr&, i&, k&, rng, ws As Worksheet, oldItem As Boolean, Rs, RsNew(1 To 10000, 1 To 6)
Dim dic As Object, key
Set dic = CreateObject("Scripting.dictionary")
For Each ws In Sheets
    If ws.Name <> "FINISHING" Then
        lr = ws.Cells(Rows.Count, "B").End(xlUp).Row
        rng = ws.Range("B2:D" & lr).Value
        For i = 1 To lr - 1
            If Not dic.exists(rng(i, 1)) Then
              dic.Add rng(i, 1), rng(i, 2) & "|" & rng(i, 3)
            Else
            On Error Resume Next
                dic(rng(i, 1)) = (Split(dic(rng(i, 1)), "|")(0) + rng(i, 2)) & "|" & (Split(dic(rng(i, 1)), "|")(1) + rng(i, 3))
            On Error GoTo 0
            End If
        Next
    End If
Next
With Worksheets("FINISHING")
    lr = .Cells(Rows.Count, "B").End(xlUp).Row
    Rs = .Range("A2:F" & lr).Value
    For Each key In dic.keys
        oldItem = False
        For i = 1 To lr - 1
            If Rs(i, 2) = key Then
                oldItem = True ' indicate new Item NOT found
                Rs(i, 4) = Split(dic(key), "|")(0)
                Rs(i, 5) = Split(dic(key), "|")(1)
                On Error Resume Next
                    Rs(i, 6) = Rs(i, 3) + Rs(i, 5) - Rs(i, 4)
                On Error GoTo 0
            End If
        Next
        If Not oldItem Then ' new Item found
            k = k + 1
            RsNew(k, 1) = lr - 1 + k
            RsNew(k, 2) = key
            RsNew(k, 3) = 0
            RsNew(k, 4) = Split(dic(key), "|")(0)
            RsNew(k, 5) = Split(dic(key), "|")(1)
            On Error Resume Next
                RsNew(k, 6) = RsNew(k, 3) + RsNew(k, 5) - RsNew(k, 4)
            On Error GoTo 0
        End If
    Next
.Range("A2").Resize(lr - 1, 6).Value = Rs
.Range("A" & lr + 1).Resize(lr - 1, 6).Value = RsNew
lr = .Cells(Rows.Count, "B").End(xlUp).Row
.Range("A2:F" & lr).Borders.LineStyle = xlContinuous
.Range("C2:F" & lr).NumberFormat = "#,##0.00"
End With
End Sub

Originally Posted by tubrak

after run the macro sales gives 280 how?

You must exclude the "right result" sheet.

In this line of the macro you must remove the sheets that should not be considered!

Please Login or Register  to view this content.

You're right, my macro has problems with the "*" considering it as a wildcard.
I changed the focus to search, I append the updated code and in the file you will see the sheet "check" with the verification of the results.

Please Login or Register  to view this content.

Glad we could help & thanks for the feedback