+ Reply to Thread
Results 1 to 7 of 7

Get unique values from column and get Counts and Sums based on these values

  1. #1
    Registered User
    Join Date
    10-06-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    8

    Get unique values from column and get Counts and Sums based on these values

    I had a worksheet (please see attached) with 2 columns -- column A is list of "volunteers" and column B is corresponding donations solicited. I need to get a list all volunteers, do a count of how many donations each one solicited, and the total amount each one solicited.

    Following is my code:
    Please Login or Register  to view this content.
    This macro doesn't work in real life situation, where I would not know in advance how many "unique" volunteers there are. The formulae in the macro are totally useless since hardcoding the criteria (volunteer initials) would not be possible.

    Shall very much appreciate if some one can give help me out.

    Thank you very much
    Henry
    Attached Files Attached Files

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Get unique values from column and get Counts and Sums based on these values

    No vba needed, no formulas needed.

    Welcome to the fun, and ultimately simple, world of Pivot Tables: http://screencast.com/t/zsmVeWzy1b
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: Get unique values from column and get Counts and Sums based on these values

    Based on your sample file, you dont need VBA for this.

    in D6...
    =IFERROR(INDEX($A$2:$A$102,MATCH(0,INDEX(COUNTIF($D$5:D5,$A$2:$A$102),0,0),0)),"")
    for the count E6...
    =COUNTIF($A$2:$A$102,D6)
    For the sum F6...
    =SUMIF($A$2:$A$102,D6,$B$2:$B$103)
    all copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    10-06-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    8

    Re: Get unique values from column and get Counts and Sums based on these values

    Hi Dear

    Thanks very much for your prompt reply. My problem is Columns A & B are actually extracted from a much bigger worksheet of raw data using a macro. I am hoping to use one single macro to complete the whole process. That is why the solution did not work for me.

    Also FYI total number of donations for any one period of time varies (i.e. variable no of rows), and the number of unique volunteers varies too (could be 4 this period, 14 next period, 9 next period, and different set of people every period too).

    I don't think I have the skill to write this part of the macro.

    Thanks again!
    Henry

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,792

    Re: Get unique values from column and get Counts and Sums based on these values

    If you extend the range in my suggestion to cover your largest range, it will auto-populate, no matter how many volunteers you have. The PT suggested will also do that too, but will need to be updated when new data is added. From the sounds of it, you did not even try them, I suggest you give them both a try and then see what you think

  6. #6
    Registered User
    Join Date
    10-06-2014
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    8

    Re: Get unique values from column and get Counts and Sums based on these values

    Hi Ford

    Your formulae work perfectly with the range extended to entire column. Thanks.

    My goal, however, is still to figure a way to put your solution into a macro so I can process my raw data with just one hit of a button.

    Many thanks again.
    Henry

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Get unique values from column and get Counts and Sums based on these values

    Which is why you should use the Pivot Table, it would update itself for whatever your data would be in the original columns. My two cents.

+ 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. Count Unique values in Column based on values in other columns
    By dmschave in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 11:06 AM
  2. Unique Values based on Another Column having values >0
    By JBurton1986 in forum Excel General
    Replies: 7
    Last Post: 02-06-2014, 11:56 AM
  3. Return number of unique values based on values in other column
    By Medir in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-26-2013, 11:17 AM
  4. Replies: 1
    Last Post: 11-16-2013, 05:18 AM
  5. Sum values in one column based on unique values in another
    By deepak1987 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-11-2011, 07:37 PM

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