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

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

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

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

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