+ Reply to Thread
Results 1 to 9 of 9

Sum up a column based off adjacent column and replace values with the sum.

  1. #1
    Forum Contributor
    Join Date
    03-14-2013
    Location
    FL
    MS-Off Ver
    Excel 2013
    Posts
    123

    Sum up a column based off adjacent column and replace values with the sum.

    I have a Report that I am working on. I am trying to sum up column B where column A remains the same. Then replace all the values in column B with the sum.

    Of course a picture is worse a thousands words so I have attached an example that will be easier to explain what I'm trying to achieve.

    As a side note these columns on the report are actually columns Z and AA, However I should be able to adjust the code to the appropriate columns.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Sum up a column based off adjacent column and replace values with the sum.

    Maybe:

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    03-14-2013
    Location
    FL
    MS-Off Ver
    Excel 2013
    Posts
    123

    Re: Sum up a column based off adjacent column and replace values with the sum.

    Quote Originally Posted by JOHN H. DAVIS View Post
    Maybe:

    Please Login or Register  to view this content.

    That worked... however with over 35000 rows of data its been over 5 min and the macro is still running... I'll try disabling screen-updating anything else you can think of to speed it up?

  4. #4
    Forum Contributor
    Join Date
    03-14-2013
    Location
    FL
    MS-Off Ver
    Excel 2013
    Posts
    123

    Re: Sum up a column based off adjacent column and replace values with the sum.

    Even with Application.ScreenUpdating = False the macro is taking Forever to run.

    I don't think this will work for my situation(which is sad considering it actually does work).

    Anyone else have any other ideas?

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: Sum up a column based off adjacent column and replace values with the sum.

    Try:

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Forum Contributor
    Join Date
    03-14-2013
    Location
    FL
    MS-Off Ver
    Excel 2013
    Posts
    123

    Re: Sum up a column based off adjacent column and replace values with the sum.

    Quote Originally Posted by TMS View Post
    Try:

    Please Login or Register  to view this content.

    Regards, TMS
    Well it worked great on the example. After further investigation it looks like there are around 300,000 rows. Your macro has been running to 7 min straight and still going. My PC is pretty quick and I'm running excel 2013 64-Bit. (As a side not excel says not responding, however the CPU for excel keeps changing so it makes me think its still trying)

    I've seen people use scripting dictionaries that run almost instantaneously, however I have absolutely no idea how to right such complex macros.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

    Re: Sum up a column based off adjacent column and replace values with the sum.

    I haven't got 30,000 rows of data to test it with, so I can't really comment.

    I'm not that great with dictionaries so I can't really help there. What is the purpose of this exercise? You start with 30,000 records, some of which have multiple entries for the same value and you want each of those multiple entries to have the sum? And what do you do with that?

    Would a Pivot Table not give you the answer you want?

  8. #8
    Forum Contributor
    Join Date
    03-14-2013
    Location
    FL
    MS-Off Ver
    Excel 2013
    Posts
    123

    Re: Sum up a column based off adjacent column and replace values with the sum.

    I'm creating a Variance report on the entire production of TODAY-365.


    The database I'm pulling from is supposed to be SQL, however it is a non relational database.

    Basically for every policy I have to pull all the transactions and sum them up and compare it to what we are expected to be paid.

    I'll attach another example that is a better match to what i am actually dealing with. I wont create the After as that's the part I cant get due to the size.

    The attachment size is limited to 1M so i couldn't upload the full example. My report has 255,736 rows.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    03-14-2013
    Location
    FL
    MS-Off Ver
    Excel 2013
    Posts
    123
    the sums dont actually have to be put in every cell. After its summed up i only need one line per policy. I just asked it that way so the future macros would be easier.

+ 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. Macro to find all specific values in column and replace adjacent cell values
    By dblock02 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2013, 06:03 AM
  2. Replies: 19
    Last Post: 04-17-2013, 09:51 PM
  3. Replies: 1
    Last Post: 03-02-2013, 05:06 AM
  4. adding values as 1 string, based on adjacent column
    By TCountryman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2010, 10:51 AM
  5. Replies: 6
    Last Post: 03-04-2006, 07:30 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