+ Reply to Thread
Results 1 to 14 of 14

Merge and summarise rows with VBA

  1. #1
    Registered User
    Join Date
    02-10-2012
    Location
    Wokingham
    MS-Off Ver
    Excel 2010
    Posts
    9

    Merge and summarise rows with VBA

    I am not sure if VBA is the correct approach but I need something that is repeatable and can be run with a simple press of a button, Unfortunately I am a complete noob to VBA so any assistance would be appreciated.

    We have a growing currently 5000+ rows CSV file that is produced from our quotation system, and this is growing on a daily/monthly basis

    To make the data useful the values need to be summarised so that there is just one record (row) per unique quotation no, that contains all the row data, but with the value totalled for all the matching items.

    I have tried using Pivot tables and looking at other solutions on this Forum, but don't know enough to work out how to change the macros I have found to work with our data.

    I have attached a very small sample spreadsheet with the core data, and any help would be much appreciated

    Andy

    PS Moderators this is my first post after trawling through this forum and hopfully this message abides by the rules
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Merge and summarise rows with VBA

    This works for your example. It replaces the original data.
    Please Login or Register  to view this content.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Merge and summarise rows with VBA

    Hi Andy and welcome to the forum,

    I think a Pivot Table without any code is the answer. See the attached.

    If your data keeps growing then you can base the pivot table off a Dynamic Named Range that will grow with the number of rows of data you have.
    See: http://www.beyondtechnology.com/geeks007.shtml

    If that doesn't do it and you really need a Button to click that refreshes the Pivot Table then give us a little bigger sample and tell us what other fields besides account number and total you need to see.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    02-10-2012
    Location
    Wokingham
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Merge and summarise rows with VBA

    Marvin

    Thanks for the quick reply, I need to see all the fields, as after the file is summarised there is stii a deduplication process that needs to be run. We can often quote multiple times for the same job to different companies, but it is only a single opportunity. If we used all of the data it would overstate our opportnities by an average of 5 to 1 (not a good thing :-) )

    So after this first process we then have to remove all quotations which match on project ref and project name, this has to be done after the merge otherwise we would remove correct data.

    Andy

  5. #5
    Registered User
    Join Date
    02-10-2012
    Location
    Wokingham
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Merge and summarise rows with VBA

    Stephen

    This works without any problems, thank you very much, now just need to remove the duplicate transactions and we will have a very usable data source. Could this be added into the same VB routine?

    The rows that then need removing entirely are those where the project name and the project ref are the same

    Andy

    PS I can supply more data if needed

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Merge and summarise rows with VBA

    Hey Andy,

    If you need to see all the fields then using Advanced Filters is the normal method of attack.
    See the attached to see if it does what you need.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-10-2012
    Location
    Wokingham
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Merge and summarise rows with VBA

    Quote Originally Posted by MarvinP View Post
    Hey Andy,

    If you need to see all the fields then using Advanced Filters is the normal method of attack.
    See the attached to see if it does what you need.
    The date is irrelevant for the filtering, but it would be used to show when the first quotation for a particular project was created. It is also used to shown in which month/quarter the quote was created, and therefore part of the salespersons forecast (Pivot Table). This would be after the duplicates were removed.

    Andy

  8. #8
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Merge and summarise rows with VBA

    Yes please, with before and after.

  9. #9
    Registered User
    Join Date
    02-10-2012
    Location
    Wokingham
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Merge and summarise rows with VBA

    Quote Originally Posted by StephenR View Post
    Yes please, with before and after.
    Stephen

    Worksheet attached with before and after data. The befor data is after your VBA scripr ran and removed and totalled the lines.

    Andy
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Merge and summarise rows with VBA

    Andy - it's not clear to me which lines should be removed, could you elaborate?

  11. #11
    Registered User
    Join Date
    02-10-2012
    Location
    Wokingham
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Merge and summarise rows with VBA

    Stephen

    I will edit the spreadsheet with comments to show and then post

  12. #12
    Registered User
    Join Date
    02-10-2012
    Location
    Wokingham
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Merge and summarise rows with VBA

    Quote Originally Posted by andy_kerslake View Post
    Stephen

    I will edit the spreadsheet with comments to show and then post
    Stephen

    As requested spreadsheet with comments added and notes, hopefully this will be easier to understand.

    Andy
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    02-10-2012
    Location
    Wokingham
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Merge and summarise rows with VBA

    Quote Originally Posted by andy_kerslake View Post
    Stephen

    As requested spreadsheet with comments added and notes, hopefully this will be easier to understand.

    Andy
    I just noticed the status of some of the duplicates. This means the rule has to be changed slightly, when removing the entries the status must be equal to open or lost, closed items never have duplicates.


    Andy

  14. #14
    Registered User
    Join Date
    02-10-2012
    Location
    Wokingham
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Merge and summarise rows with VBA

    Quote Originally Posted by andy_kerslake View Post
    I just noticed the status of some of the duplicates. This means the rule has to be changed slightly, when removing the entries the status must be equal to open or lost, closed items never have duplicates.


    Andy
    Stephen

    After some more experimentation with Excel 2010 the Data Function "remove duplicates", removes all the correct records if Status, Project Ref and Project Name are selected.

    Andy

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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