VBA to merge duplicate rows and sum values in certain column with adjustments

J

jms3564

New Member
Joined
Jan 26, 2024
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I have a similar issue as the poster in this thread: VBA to merge duplicate rows and sum values in certain column

I took the code and made the adjustments to look at column A and column I for total quantity. The issue I have is that the quantity is not really adding together, rather combining the two numbers. So if there are 3 parts with quantity 1, I am ending up with 111 as the quantity and not 3.

How do I get them to actually add together?

1706290106951.png


Sub merge_duplicate()

Dim Rng As Range, Dn As Range, nRng As Range
Dim n As Long, lr As Long, Txt As String

Application.ScreenUpdating = False

lr = Range("A" & Rows.Count).End(3).Row
Set Rng = Range("A2:A" & lr)
Set nRng = Range("A" & lr + 1)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

For Each Dn In Rng
Txt = Dn.Value
If Not .Exists(Txt) Then
.Add Txt, Dn.Offset(, 8)
Else
.Item(Txt).Value = .Item(Txt).Value + Dn.Offset(, 8).Value
Set nRng = Union(nRng, Dn)
End If
Next

nRng.EntireRow.Delete
End With
Application.ScreenUpdating = True
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
@jms3564
do you mean like this ?
aut.xlsm
ABCDEFGHI
2AA11
3ASDD320
4AA30
5DF310
6BB33330
7BB33322
8DF3112
9ASDD311
REP


result
aut.xlsm
ABCDEFGHI
2AA41
3ASDD331
4DF3122
5BB33352
REP

the code works for me.
 
Upvote 0
abdelfattah said:
@jms3564
do you mean like this ?
aut.xlsm
ABCDEFGHI
2AA11
3ASDD320
4AA30
5DF310
6BB33330
7BB33322
8DF3112
9ASDD311
REP


result
aut.xlsm
ABCDEFGHI
2AA41
3ASDD331
4DF3122
5BB33352
REP

the code works for me.
Click to expand...
So the first thing I found that was wrong on my side was that they were set as text and not general. So that made it better, but still not correct. What is strange is that it does work correctly with your example and the test one I made up copying yours. Now when I run it on my example with 3 sets, it starts by taking the first duplicate and setting the numbers side by side (11 instead of 2) and then adds the third duplicate (12 instead of 3)


It starts like this
NumberPart Type / Document TypePart Category / Document CategoryDescriptionLifecycle PhaseRevMake/BuyU/MTotal Quantity
11322-0000-101
A30-9000-4336
A30-900-4342
11322-0000-101
A30-9000-4336
A30-900-4342
11322-0000-101
A30-9000-4336
A30-900-4342

And then after I run the code it turns into this


Number
Total Quantity
11322-0000-1012
A30-9000-43372
A30-900-43424

So it adds the number together on the second time around but not the first time
 

Attachments

  • 1706297447660.png
    1706297447660.png
    22.8 KB · Views: 8
  • 1706297458757.png
    1706297458757.png
    434 bytes · Views: 8
Upvote 0
I have no idea why do that for you!!
this is what I got
aut.xlsm
ABCDEFGHI
1NumberPart Type / Document TypePart Category / Document CategoryDescriptionLifecycle PhaseRevMake/BuyU/MTotal Quantity
211322-0000-103
3A30-9000-43318
4A30-900-4346
REP

but I suggest to create new workbook , maybe the problem from it .
 
Upvote 0
example bom report.xls
ABCDEFGHI
1NumberPart Type / Document TypePart Category / Document CategoryDescriptionLifecycle PhaseRevMake/BuyU/MTotal Quantity
211322-0000-10aaaaaaaElectricalaaaaaaaPilot1MakeEA1
3A30-9000-433aaaaaaaElectricalbbbbbbbProduction2BuyEA6
4A30-9000-434aaaaaaaElectricalcccccccProduction3BuyEA2
5J96-0001-001aaaaaaaElectricaldddddddProduction4BuyEA6
610303-1008-01aaaaaaaElectricaleeeeeeeProduction5BuyEA2
710564-2520-08aaaaaaaElectricalfffffffffffffffPilot6MakeEA12
811053-4521-01aaaaaaaElectricalgggggggPilot7BuyEA5
911073-1015-01aaaaaaaElectricalhhhhhhhPilot8BuyEA12
1011073-1016-03aaaaaaaElectricaliiiiiiiiiiiiiiPilot9MakeEA12
1111322-0000-10aaaaaaaElectricalaaaaaaaPilot10MakeEA1
12A30-9000-433aaaaaaaElectricalbbbbbbbProduction11BuyEA6
13A30-9000-434aaaaaaaElectricalcccccccProduction12BuyEA2
14J96-0001-001aaaaaaaElectricaldddddddProduction13BuyEA6
1510303-1008-01aaaaaaaElectricaleeeeeeeProduction14BuyEA2
1610564-2520-08aaaaaaaElectricalfffffffffffffffPilot15MakeEA12
1711053-4521-01aaaaaaaElectricalgggggggPilot16BuyEA5
1811073-1015-01aaaaaaaElectricalhhhhhhhPilot17BuyEA12
1911073-1016-03aaaaaaaElectricaliiiiiiiiiiiiiiPilot18MakeEA12
2011322-0000-10aaaaaaaElectricalaaaaaaaPilot19MakeEA1
21A30-9000-433aaaaaaaElectricalbbbbbbbProduction20BuyEA6
22A30-9000-434aaaaaaaElectricalcccccccProduction21BuyEA2
23J96-0001-001aaaaaaaElectricaldddddddProduction22BuyEA6
2410303-1008-01aaaaaaaElectricaleeeeeeeProduction23BuyEA2
2510564-2520-08aaaaaaaElectricalfffffffffffffffPilot24MakeEA12
2611053-4521-01aaaaaaaElectricalgggggggPilot25BuyEA5
2711073-1015-01aaaaaaaElectricalhhhhhhhPilot26BuyEA12
2811073-1016-03aaaaaaaElectricaliiiiiiiiiiiiiiPilot27MakeEA12
Sheet0



So that is where it starts and then when I run this is what I get. I have tried doing it in a new workbook but that doesn't change it. Could it be the dashes in my column A causing it?

after macro.xls
ABCDEFGHI
1NumberPart Type / Document TypePart Category / Document CategoryDescriptionLifecycle PhaseRevMake/BuyU/MTotal Quantity
211322-0000-10aaaaaaaElectricalaaaaaaaPilot1MakeEA12
3A30-9000-433aaaaaaaElectricalbbbbbbbProduction2BuyEA72
4A30-9000-434aaaaaaaElectricalcccccccProduction3BuyEA24
5J96-0001-001aaaaaaaElectricaldddddddProduction4BuyEA72
610303-1008-01aaaaaaaElectricaleeeeeeeProduction5BuyEA24
710564-2520-08aaaaaaaElectricalfffffffffffffffPilot6MakeEA1224
811053-4521-01aaaaaaaElectricalgggggggPilot7BuyEA60
911073-1015-01aaaaaaaElectricalhhhhhhhPilot8BuyEA1224
1011073-1016-03aaaaaaaElectricaliiiiiiiiiiiiiiPilot9MakeEA1224
Sheet0
 
Upvote 0
this is what I got
aut.xlsm
ABCDEFGHI
1NumberPart Type / Document TypePart Category / Document CategoryDescriptionLifecycle PhaseRevMake/BuyU/MTotal Quantity
211322-0000-10aaaaaaaElectricalaaaaaaaPilot1MakeEA3
3A30-9000-433aaaaaaaElectricalbbbbbbbProduction2BuyEA18
4A30-9000-434aaaaaaaElectricalcccccccProduction3BuyEA6
5J96-0001-001aaaaaaaElectricaldddddddProduction4BuyEA18
610303-1008-01aaaaaaaElectricaleeeeeeeProduction5BuyEA6
710564-2520-08aaaaaaaElectricalfffffffffffffffPilot6MakeEA36
811053-4521-01aaaaaaaElectricalgggggggPilot7BuyEA15
911073-1015-01aaaaaaaElectricalhhhhhhhPilot8BuyEA36
1011073-1016-03aaaaaaaElectricaliiiiiiiiiiiiiiPilot9MakeEA36
REP
 
Upvote 0
How is your data getting into your spreadsheet ?
Your images indicate that your Qty column is left aligned which is a strong indicator that Excel is reading that column as text. You have even indicated that it is being formatted as Text.
Using "+" on a text field is a concatenate operator and does the same thing as "&"

I have added the code in blue which will convert it on the fly. Ideally you want to sort the data issue out before running your macro.

Rich (BB code): 
Sub merge_duplicate()
Dim Rng As Range, Dn As Range, nRng As Range
Dim n As Long, lr As Long, Txt As String

Application.ScreenUpdating = False

lr = Range("A" & Rows.Count).End(3).Row
Set Rng = Range("A2:A" & lr)
Set nRng = Range("A" & lr + 1)

' Ideally this would not be required and handled in a previous step
' Convert Column I from Text to Number
With Rng.Offset(, 8)
    .NumberFormat = "General"       ' Change to appropriate number format
    .Value = .Value
End With

With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng
        Txt = Dn.Value
        If Not .Exists(Txt) Then
            .Add Txt, Dn.Offset(, 8)
        Else
            .Item(Txt).Value = .Item(Txt).Value + Dn.Offset(, 8).Value
        Set nRng = Union(nRng, Dn)
        End If
    Next
    
    nRng.EntireRow.Delete
End With
Application.ScreenUpdating = True

End Sub
 
Upvote 0
Solution
Alex Blakenburg said:
How is your data getting into your spreadsheet ?
Your images indicate that your Qty column is left aligned which is a strong indicator that Excel is reading that column as text. You have even indicated that it is being formatted as Text.
Using "+" on a text field is a concatenate operator and does the same thing as "&"

I have added the code in blue which will convert it on the fly. Ideally you want to sort the data issue out before running your macro.

Rich (BB code): 
Sub merge_duplicate()
Dim Rng As Range, Dn As Range, nRng As Range
Dim n As Long, lr As Long, Txt As String

Application.ScreenUpdating = False

lr = Range("A" & Rows.Count).End(3).Row
Set Rng = Range("A2:A" & lr)
Set nRng = Range("A" & lr + 1)

' Ideally this would not be required and handled in a previous step
' Convert Column I from Text to Number
With Rng.Offset(, 8)
    .NumberFormat = "General"       ' Change to appropriate number format
    .Value = .Value
End With

With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng
        Txt = Dn.Value
        If Not .Exists(Txt) Then
            .Add Txt, Dn.Offset(, 8)
        Else
            .Item(Txt).Value = .Item(Txt).Value + Dn.Offset(, 8).Value
        Set nRng = Union(nRng, Dn)
        End If
    Next
   
    nRng.EntireRow.Delete
End With
Application.ScreenUpdating = True

End Sub
Click to expand...
I'm getting the data out of a report being run through another application.

Those additionas solved the issue. Weird that I did the change manually for the column and that didn't work but having it in the VBA worked perfectly. Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
how change code from range to TABLE and from sheet to multiple
Replies
8
Views
589
Maklil
M
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
933
Alex Blakenburg
A
S
  • Solved
Copy values only without specific column formats VBA
Replies
9
Views
387
sofas
S
O
  • Solved
VBA Code to Find Duplicate Values in a Range and Add a Number at the End
2
Replies
12
Views
664
OilEconomist
O
M
  • Solved
Repeatedly finding the sum of values in a dataset between two endpoints
Replies
2
Views
230
mr_steveb
M

Forum statistics

Threads
1,223,886
Messages
6,175,194
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top