+ Reply to Thread
Results 1 to 9 of 9

Sum values in one column based on unique values in another

  1. #1
    Registered User
    Join Date
    07-11-2011
    Location
    sydney, australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Sum values in one column based on unique values in another

    Hi,

    I'll get right into it:

    I have the following data which is a small sample from a large pool that constantly changes:

    Column A Column B
    1 156234
    2 178292
    2 34023
    3 10934
    4 156705

    I need to get it to look like this:

    Column D Column E
    1 156234
    2 212315
    3 10934
    4 156705

    I need to use VBA arrays to attain the unique values from column A and sum Column B accordingly. I have used PivotTables previously, but given the data constantly changes i thought VBA Arrays would be best suited to make it efficient as excel formulas take sometime to calculate with 30,000+ records.


    Thanks for you help.

    Regards
    Deepak
    Last edited by deepak1987; 07-11-2011 at 07:38 PM.

  2. #2
    Registered User
    Join Date
    06-29-2011
    Location
    MUMBAI
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    85

    Re: Sum values in one column based on unique values in another

    try this,
    Please Login or Register  to view this content.
    regards

  3. #3
    Registered User
    Join Date
    06-29-2011
    Location
    MUMBAI
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    85

    Re: Sum values in one column based on unique values in another

    for the values u can try,
    =VLOOKUP(a2,a:a,1,0)

  4. #4
    Registered User
    Join Date
    07-11-2011
    Location
    sydney, australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sum values in one column based on unique values in another

    thanks vipul, but that is taking a little while to calculate:

    I tried the following with some help from google:
    Please Login or Register  to view this content.
    ItemList is ColumnA and UniqueItemList is the Output of unique values and then there is count as well as a sum of ColumnB - however it takes a while to calculate.

    I need to put this into an array within VBA to make it more efficient.
    Last edited by romperstomper; 07-11-2011 at 04:48 AM.

  5. #5
    Registered User
    Join Date
    06-29-2011
    Location
    MUMBAI
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    85

    Re: Sum values in one column based on unique values in another

    whatever way u will need to calculate.
    A VBA wld perform operation on its own.
    u can apply the formula or even this mit be rit.coz i dont exactly know ur data.

  6. #6
    Registered User
    Join Date
    07-11-2011
    Location
    sydney, australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sum values in one column based on unique values in another

    I have attached what i have done so far.

    It take quite sometime to calculate through some 250 odd records i have just created as a dummy. when there are 10,000+ records, it takes a significant amount of time.

    Arrays in VBA are supposed to make things significantly quicker but i am not not sure how to create this.

    Was wondering how to convert this code into VBA Array.

    Thanks
    Attached Files Attached Files

  7. #7
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Sum values in one column based on unique values in another

    Do you really need VBa for this?

    In C2
    Please Login or Register  to view this content.
    Drag/ Fill Down until a blank is returned

    In D2
    Please Login or Register  to view this content.
    In E2
    Please Login or Register  to view this content.
    Drag/ Fill both Down to suit the results in Column C.

    N.B.
    Your profile indicates you are using 2003, but your sample is clearly 2007 or above.
    For 2007 and above use this in C2
    Please Login or Register  to view this content.

    [EDIT]
    For Files with 30000 plus rows adjust the lookup range size e.g.
    Please Login or Register  to view this content.

    I can't see VBa being any faster, I might be wrong.
    Attached Files Attached Files
    Last edited by Marcol; 07-11-2011 at 05:59 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Sum values in one column based on unique values in another

    in D1:

    PHP Code: 
    =SUMPRODUCT(($A$1:$A$270=ROW())*($B$1:$B$270)) 
    in C1
    PHP Code: 
    =ROW() 
    But if you prefer to work with arrays: (clear column E first)

    Please Login or Register  to view this content.
    Last edited by snb; 07-11-2011 at 06:37 AM.



  9. #9
    Registered User
    Join Date
    07-11-2011
    Location
    sydney, australia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sum values in one column based on unique values in another

    Thanks, that works

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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