+ Reply to Thread
Results 1 to 10 of 10

Excel VBA Code Instead of SumIf Array Formula for Unique Values

  1. #1
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Question Excel VBA Code Instead of SumIf Array Formula for Unique Values

    Hello Forum,

    I have a set of data that is approximately 50,000 rows and need to do a summing function of the number of distinct values in one column that share a common value in another column. I've used the array formula {=SUM(IF(FREQUENCY(IF($B$2:$B$43134=B2,$A$2:$A$43134),$A$2:$A$43134)>0,1))} in the past and while it works it is EXTREMELY slow on our company network and my little HP laptop that could...takes hours and I literally need to leave it running overnight on the docking station and pray there isn't a forced reboot going on that night! I also have to do a similar sum if logic for two other columns. The results feed a model that I use pivot tables to determine percentages eligible, totals, etc.

    So I'm looking for VBA code that will loop through the data rows much faster than using the above type array formula or VBA that uses formula logic. I'd like to be able to use a macro button to control when the event occurs, but would like the code to run through every row and provide a result for each. I saw this link for count-ifs, but couldn't make it work for my situation and I'm hoping for similar lightning speed!

    http://www.excelforum.com/excel-prog...-countifs.html

    I've attached a much more simplified sample workbook that hopefully shows what I'm trying to accomplish, but essentially values in column A all tie to a value in column B. The relationship could be 1:1 or 1:many, basically it's the number of products (column A) on an account (column B). So for each value in column B, sum the total number of unique values in column A and put the result in column C. So for my sample workbook account 8888 has 3 unique values, 9797 has 2, and 1111 has 1. I tried to record a macro and it works one at a time, but would image even if I got it to work on the entire range with a single click of a command button it may still be very slow.

    UnqVals.PNG

    As always appreciate any help and thank you in advance!

    Best regards,
    Chris
    Attached Files Attached Files

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Excel VBA Code Instead of SumIf Array Formula for Unique Values

    Hello excelforumkeys,

    The macro below has been added to the attached workbook and is called by the command button. Placing the count next to each id is what slows the operation to a crawl. To speed things up I have the unique list and counts output to "Sheet2". All the data values are moved into RAM by using arrays. This eliminates the overhead encountered with Range objects. Try this out and let me know the results.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Re: Excel VBA Code Instead of SumIf Array Formula for Unique Values

    Hi Leith,

    First thanks for the fast response and love the speed of the macro! Also, I believe I can make your approach work for my intended purposes with a vlookup back to the specific line items.

    However, the macro isn't doing what I wanted and I'm sure it's due to my inability to properly convey the goal. The macro as you've provided sums the total occurrences of each unique Product for a specific Account. What I actually need is to sum the total unique Products on each Account.

    Thus, using your approach, I suppose I'd only need Account number and the Ttl Unique columns. In the sample I've used, the results would be:

    UnqVals2.PNG

    Since:
    Unique values associated with 8888: 6262, 5555, 5353
    Unique values associated with 9797: 7171, 3535
    Unique values associated with 1111: 2222

    Am I making sense? If not, let me know how I can clarify or what questions you might have. Hoping you can tweak the code slightly to make this work.

    Appreciate your help Leith!
    Chris

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Excel VBA Code Instead of SumIf Array Formula for Unique Values

    Hello Chris,

    Sorry, I misunderstood what you wanted. This workbook produces the output you showed in the picture. The new code is shown below and has been added to the attached workbook.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Re: Excel VBA Code Instead of SumIf Array Formula for Unique Values

    Leith, this is so cool! It just needs a little tweaking to work I think.

    When I assigned and ran the macro on my real data set of over 43K rows (took only about 10 seconds compared to 4 hours with the array formula!!!) it yielded the correct results on the destination worksheet.

    However, instead of placing the results in columns A and B in that destination worksheet, it put them in columns C and D. I think it's because I modified the code
    Please Login or Register  to view this content.
    to be
    Please Login or Register  to view this content.
    since that's where the comparison data for Products (column C) and Accounts (column D) resides in my real workbook and I assumed that SrcWks.Range is to set that. Since that change in code gave me the correct results, just not placed where I'd expect them to be, I'm guessing that somehow (and my ignorant brain can't figure it out) the code is using that source range to determine where to put the results in the destination sheet (given they ended up in C and D). Or I may be completely off base, who knows!

    So a couple follow up questions:

    1) Can you clue me in on how that piece of the code works and how I can get the results under A and B in the destination sheet?

    2) Also on a related note, I have two more scenarios where I'll need to use the same logic, but where the columns of data aren't next to one another. I'm need to also find the sum of unique Products (column C) to each Store (column R), and the sum of unique Accounts (column D) to Store (column R). I believe I can figure out the destination part for these results once I see your answer above to question 1, but would I also use the same set up for source range??? I.e., respectively:
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    That doesn't seem right to me as I'd think it'd be looking at all the columns in between, but since I'm fairly new to coding, I don't really know.

    Again, really appreciate the help here Leith and this is going to save me a TON of time once done. Can't believe I'm this fired up about a spreadsheet and VBA!

    Regards,
    Chris

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Excel VBA Code Instead of SumIf Array Formula for Unique Values

    Hello Chris,

    You're right about setting the destination range from the source range. The destination was using the first row of the source range as its starting column. Here is updated macro. The source starts in column "C2:D2" and the destination in "A2:B2". The changes are highlighted in blue.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Re: Excel VBA Code Instead of SumIf Array Formula for Unique Values

    Leith, that worked perfectly for placing in the desired destination columns, so thank you! I am SO close to locking this down (with your expert assistance of course).

    I managed to take that additional change along with a little deeper study into what your code was doing (ok, some serious very slightly educated guessing at best!) and modify for the additional scenarios I outlined in question 2) above, so I think I'm good there after running those and spot checking the results.

    Couple final questions (hopefully) on the line of code towards the end that was clearing contents:
    Please Login or Register  to view this content.
    Did you include that as a safety measure to ensure if the results were smaller on a "refresh" that there would not be extraneous data in the results?

    I ask because when I run my other similar summing scenarios for unique products to store and unique accounts to store that place those results in columns E & F and I & J respectively, it wipes out my original scenario's results that were in columns A & B, which I don't want to happen. Is there a way to modify that code line to only clear the contents of the columns on the destination sheet AND leave the column headers in place?

    When I delete the line of code, then that's not an issue but then I run the risk of keeping "old" data on a refresh if I don't remember to clear previous results.

    Thanks much again,
    Chris

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Excel VBA Code Instead of SumIf Array Formula for Unique Values

    Hello Chris,

    The destination range is assumed to have a header in row 1. The UsedRange will start at this row. Since it is the lowest row number, there is no need to check further. The Offset(1, 0) always starts the data in row 2. The UsedRange includes all entries on the worksheet as well as formatting like borders and cell background colors. Everything except row 1 is cleared.

  9. #9
    Registered User
    Join Date
    07-15-2013
    Location
    Arizona
    MS-Off Ver
    Excel 2016
    Posts
    64

    Thumbs up Re: Excel VBA Code Instead of SumIf Array Formula for Unique Values

    Ahhhh...thanks for the explanation on how that's working. I'll fiddle with some other options such as placing results for each lookup in different sheets or setting a more specific clear contents line of code in.

    Seriously Leith, thanks so much for sticking with me and taking the time to help. I really appreciate you and of course the forum in general. Abosultely amazing!

    Until next time...

    In Gratitude I Remain,
    Chris

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Excel VBA Code Instead of SumIf Array Formula for Unique Values

    Hello Chris,

    You're welcome. It's been fun.

+ 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. [SOLVED] Array formula to extract and sort unique values from two worksheets
    By rshukla in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-07-2013, 06:11 AM
  2. add to array formula to only return unique values
    By jason892 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-11-2013, 06:39 AM
  3. [SOLVED] Count unique values in list but NOT using ARRAY formula
    By alx0101 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-15-2013, 08:15 AM
  4. Replies: 3
    Last Post: 11-24-2011, 06:11 AM
  5. Replies: 6
    Last Post: 06-14-2011, 07:18 AM

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