Solved

vba to merge into one amount

Posted on 2016-08-14
12
52 Views
Last Modified: 2016-08-17
Can an expert help me out with this please.

In my spread sheet there are codes in column ‘C’ Some of these I do not want to touch, others I need to merge the amounts.

So, I think I need VBA code where I tell it what codes to look at.

In the attached spread sheet you will see I have ‘IC123,4 and 5 which I do not want to touch. I have codes 700001 through 700021 which I need the VBA to work on.

I need to look at each code and where there are more than one of the same code, total them, put the total in the first occurrence and delete the other rows.

So i.e. 700001 has 4 amounts they total 65.00 so replace 15 [first amount] with 65.00 and delete rows with 20, 25 and 5. Then do this for all other codes starting with  7 in column ‘C’

17/08/2016      700001      15
17/08/2016      700001      20
17/08/2016      700001      25
17/08/2016      700001      5

Many thanks
Find-and-merge.xlsx
0
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 41755685
What do you mean by "starting with  7 in column ‘C’"?

Do you mean that you don't want to do anything with rows 1 to 13, and you want to start merging at row 14?

Will the first 13 rows always be there? In other words can there be less or more rows that you don't want to touch?
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41755711
Please try this:
Sub Demo()

    Dim lngRow As Long
    Dim lngLastRow As Long

    lngLastRow = Worksheets("Start").Columns("C").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

    With Worksheets("Start").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("C14"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("C14:D" & lngLastRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    For lngRow = lngLastRow To 2 Step -1
        If IsNumeric(Cells(lngRow, 3)) Then
            If Cells(lngRow, 3).Value = Cells(lngRow - 1, 3).Value Then
                Cells(lngRow, 4).Value = Cells(lngRow, 4).Value + Cells(lngRow - 1, 4).Value
                Cells(lngRow - 1, 4).EntireRow.Delete
            End If
        End If
    Next

End Sub

Open in new window

0
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41755926
Why not use a PivotTable? A PivotTable requires no VBA and can be refreshed simply

Here's an introduction to PivotTables
Find-and-merge.xlsx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jagwarman
ID: 41755962
When I said "starting with  7 in column ‘C’"?  that meant any code that starts with a 7, like 700001,700002 etc. I cannot use Pivot for various reasons otherwise I would.

But one thing I forgot in my original request was that the narrative in column 'F' must also be the same so for instance:

700002 would be merged to give a total of 71.92
700005 would not be merged

N/A      12/08/2016      700002       £65.10      N/A      DM6 XYZ MCE  10-08-16
N/A      12/08/2016      700002       £6.82      N/A      DM6 XYZ MCE  10-08-16
N/A      12/08/2016      700005       £20.50      N/A      DM6 ME1 STMT 10-08-16
N/A      12/08/2016      700005       £5.00      N/A      DM6 cards ME1 11-08-16

sorry for confusion
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41756135
Try this:
Sub Demo()

    Dim lngRow As Long
    Dim lngLastRow As Long

    lngLastRow = Worksheets("Start").Columns("C").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

    With Worksheets("Start").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("C14"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("C14:D" & lngLastRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    For lngRow = lngLastRow To 2 Step -1
        If Left(Cells(lngRow, 3), 1) = 7 Then
            If Cells(lngRow, 3).Value = Cells(lngRow - 1, 3).Value And Cells(lngRow, 6).Value = Cells(lngRow - 1, 6) Then
                Cells(lngRow, 4).Value = Cells(lngRow, 4).Value + Cells(lngRow - 1, 4).Value
                Cells(lngRow - 1, 4).EntireRow.Delete
            End If
        End If
    Next

End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 41757428
sorry for delay in getting back. Thanks for your solutions however they do not appear to merge the amounts, so if I have

N/A      12/08/2016      700002       £65.10      N/A      DM6 XYZ MCE  10-08-16
 N/A      12/08/2016      700002       £6.82      N/A      DM6 XYZ MCE  10-08-16

I should end up with one row

N/A      12/08/2016      700002       £71.92 N/A      DM6 XYZ MCE  10-08-16

Regards
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41757430
I tested it on the file you uploaded and it worked as expected.
If the layout is diferent in your production file you will have to addapt the code.
0
 

Author Comment

by:Jagwarman
ID: 41757456
I have tested and re-tested and they remove rows but do not keep the totals.

so as I said above:

N/A      12/08/2016      700002       £65.10      N/A      DM6 XYZ MCE  10-08-16
  N/A      12/08/2016      700002       £6.82      N/A      DM6 XYZ MCE  10-08-16

 I should end up with one row

 N/A      12/08/2016      700002       £71.92 N/A      DM6 XYZ MCE  10-08-16

but I end up with

 N/A      12/08/2016      700002       £65.10 N/A      DM6 XYZ MCE  10-08-16
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41757468
Can you upload a sample file?
0
 

Author Comment

by:Jagwarman
ID: 41758907
As requested
Find-and-Move-JK.xlsx
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 41759107
There are changes in this file that you didn't mention...
Run the macro in the attached file, it does exactly as you ask.
Find-and-Move-JK.xlsm
0
 

Author Closing Comment

by:Jagwarman
ID: 41759682
Many thanks and apologies for any confusion.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question