+ Reply to Thread
Results 1 to 27 of 27

insert three columns with merge duplicate items and calculation across sheets

  1. #1
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    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 .
    Attached Files Attached Files
    Last edited by tubrak; 06-19-2022 at 10:55 PM.

  2. #2
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    México
    MS-Off Ver
    Excel 2013
    Posts
    175

    Re: insert three columns with merge duplicate items and calculation across sheets

    Try this:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards Dante Amor

  3. #3
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    great !
    two modifying please
    1- sorry I don't mentioned from the beginning .sometimes there is new item into one of sheets but not existed in sheet finshing should be add it and dd hyphen in column C .
    2- I want add numberformat foe each value like this "#,##0.00"
    thanks

  4. #4
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    México
    MS-Off Ver
    Excel 2013
    Posts
    175

    Re: insert three columns with merge duplicate items and calculation across sheets

    Try this:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    thanks again
    yesterday strange case occurs !!
    it gives wrong values it seems arranging the columns are not correct and today occures the same thing
    just compare the result in sheet FINISHING when running the code with the right result in last sheet.
    Attached Files Attached Files

  6. #6
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    México
    MS-Off Ver
    Excel 2013
    Posts
    175

    Re: insert three columns with merge duplicate items and calculation across sheets

    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.
    Last edited by Dante Amor; 06-23-2022 at 05:45 PM.

  7. #7
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    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.
    it's existed into two sheets ITEMS,SHEET2 , if as you said 15 then shouldn't give 34 ( it seems 15+15+4) .the right 15+15
    the problem when shows the values are wrong .the returns shows me 34 but the right 4 , and the balance shows me 4 but the right is 224 ,did you see the values after run the macro ?
    it seems the return add over sale (30+4=34) and the return replace of the balance .

  8. #8
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    México
    MS-Off Ver
    Excel 2013
    Posts
    175

    Re: insert three columns with merge duplicate items and calculation across sheets

    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

  9. #9
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    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 .
    Attached Files Attached Files
    Last edited by tubrak; 06-24-2022 at 02:56 AM.

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,382

    Re: insert three columns with merge duplicate items and calculation across sheets

    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

  11. #11
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    @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?
    Last edited by tubrak; 06-24-2022 at 02:55 AM.

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,382

    Re: insert three columns with merge duplicate items and calculation across sheets

    Here it is. Pls highlight where the issue is.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    @bebo021999
    very strange !!! it works
    do me favor please add this my asking as I has ever said,please?
    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 .

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,052

    Re: insert three columns with merge duplicate items and calculation across sheets

    This highlights the difference between my result and yours.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,382

    Re: insert three columns with merge duplicate items and calculation across sheets

    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
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    @bebo021999
    thanks again ! just last thing seem you forgot showing "-" for the empty or zero cell
    and will close this thread

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,052

    Re: insert three columns with merge duplicate items and calculation across sheets

    So, you don't want to even test my code, good.

  18. #18
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    jindon
    So, you don't want to even test my code, good.
    my apologies !!!
    the matter is not as what you think , believe me I don't note it just when enter the thread I see the last post for bebo021999
    I don't enter by notification in my email if I did it ,then I saw it.thanks for informe me
    about your code extremely perfect except one thing when I add new item and contains values in columns sales & returns it will put value under column QTY shouldn't be , when add new item the column QTY is always "-" should put the values under columns sales & returns
    see the wrong in last row when I run the macro and the right in column O,P ,Q range also highlighted .
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,052

    Re: insert three columns with merge duplicate items and calculation across sheets

    Try change to
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    thanks jindon but when add new item the empty cell keep empty , doesn't add "-" .

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,052

    Re: insert three columns with merge duplicate items and calculation across sheets

    That's your formatting.
    The code copies the cell formats from the last row when the code needs to add new row(s).
    The cell format in that column have multiple formatting, so you need to set it as you want for each column before you run the code.

  22. #22
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    @jindon thanks for your solution & time
    @Dante Amor you're right , your code works but just on your attaching but not mine( strange case!!!) . even if that many thanks
    @bebo021999 thanks very much for time & helping
    great works guys

  23. #23
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    México
    MS-Off Ver
    Excel 2013
    Posts
    175

    Re: insert three columns with merge duplicate items and calculation across sheets

    Quote Originally Posted by tubrak View Post
    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.
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    @Dante Amore
    again much appreciated for your effort & assistance
    you and the others guys provide me great solution
    thanks all

  25. #25
    Forum Contributor Dante Amor's Avatar
    Join Date
    07-24-2020
    Location
    México
    MS-Off Ver
    Excel 2013
    Posts
    175

    Re: insert three columns with merge duplicate items and calculation across sheets

    Glad we could help & thanks for the feedback

  26. #26
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,052

    Re: insert three columns with merge duplicate items and calculation across sheets

    Quote Originally Posted by tubrak View Post
    thanks jindon but when add new item the empty cell keep empty , doesn't add "-" .
    Quote Originally Posted by jindon View Post
    That's your formatting.
    ......
    ......
    OOps, also need to change one line...
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  27. #27
    Forum Contributor
    Join Date
    06-17-2021
    Location
    Asia
    MS-Off Ver
    2019
    Posts
    107

    Re: insert three columns with merge duplicate items and calculation across sheets

    @jindon
    thanks again for correction

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] search & merge duplicate items based on inputbox
    By abdo meghari in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-27-2022, 12:00 PM
  2. adapting code to merge the quantity for two columns and for duplicate items
    By ABDELFATTA in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2022, 01:58 AM
  3. Replies: 1
    Last Post: 11-03-2021, 10:40 AM
  4. [SOLVED] modified code merge duplicate items for each sheet separately based on this thread
    By KalilMe in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-16-2021, 12:30 AM
  5. merge and unmerge duplicate items based on two options for each sheet
    By abdo meghari in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-21-2021, 08:51 PM
  6. [SOLVED] Merge several sheets, with different items
    By xinx in forum Excel General
    Replies: 40
    Last Post: 08-18-2013, 09:26 AM
  7. [SOLVED] Merge duplicate rows and merge the columns (approx 10 columns)
    By adfeddon in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2012, 11:19 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1