Grouping, Summing, and Displaying data based off of user inputs

Not sure if VBA is needed or a formula. I have a spreadsheet with 10,000 rows of data. The 5 columns are Dates, Site, Brand, Style, and Quantity. I have a simple SUMIFS function that will sum the quantity based on What date range, site, brand, and style the user chooses. That was easy enough...The tough part is actually summing up the individual styles and displaying those in a list.

For Example:

Date Site Brand Style Quantity
9/1/13 A.com XYZ 1A 10
9/1/13 A.com XYZ 1A 20
9/1/13 A.com XYZ 1B 5
9/1/13 A.com XYZ 1A 10
9/1/13 A.com XYZ 1B 50
9/1/13 A.com XYZ 1C 5

The above mentioned formula would simply Sum all this up and display:
Site Brand Quantity
A.com XYZ 100

What I am needing help with is being able to display this also:
Site Brand Style Quantity
A.com XYZ 1A 40
A.com XYZ 1B 55
A.com XYZ 1C 5

Keep in mind this could potentially be up to 100 styles, and the number is not static and is always changing as more data gets added. Some brands could have 1 style, and some could have 100, or more. However, the Brand and Site would always be the same.

tehneXus,

Here is the problem I have found with this. This will take everything in the list group it and sum it. If I add a company "ABC" to the end then it will group and sum that as well. The problem is I would need the VBA to look in the list of 10,000 rows that have multiple sites and brands, and say Only group and sum when date = 1/9/2013, site = A.com, and Brand = XYZ. Keep in mind, the Date, Site, and Brand are user input and will always change.

What about a date range instead of one single date?

The code is based on 3 columns match.

So far VBA works great. I just was curious about a few more tweaks. 1: How would I expand it from only showing 5 columns of data to 8. 2: I would rather the data be shown on the same sheet starting in a different cell rather than being transferred to a new sheet in the workbook. 3: How would you incorporate a date range(equal to and between the two dates the user sets) rather than one single date to look at.

1. The code goes to the last column, so the code has already dealt with no1.
2 can be adjusted, but I do not understand the 3rd

3: How would you incorporate a date range(equal to and between the two dates the user sets) rather than one single date to look at.

Start Date 9/5/2013
End Date 9/6/2013

The user would input the start date and the end date. Essentially another criteria that would be >=9/5/2013 and <=9/6/2013. Keep in mind, these dates will change depending on what the user enters.

tehneXus I need your help.

I have Data in columns A - G, and I only want B - G to display when the code is executed. Currently, it is only displaying B - E and I cannot figure out how to include F and G. Lastly, I would much rather the final product be displayed in the same sheet rather than creating a new sheet and displaying the data there. I would prefer this data to start on Cell E10 in the current/active sheet. What do I need to do?

Please Login or Register  to view this content.

F is sales and G is Cost. They would be summing just like column E(quantity) is.

Also, I just put in your line of code and it's putting it in the right spot on the sheet. However, it is copying in column A's header, but the data under it is column B's data. I would like only column B - Gs header and data.


Would you please take a look at my date range formula >= and <= I think there may be a problem because when I spot check the math it seems to over sum.
Does this make sense?

Here it is. Please let me know any questions you have. I just used dummy data and a small date range instead of YTD.Test.xlsm

tehneXus, this works perfect on a PC. However, using this on a mac is giving me an this error:

Run-time error '429':
ActiveX component can't create object

When I hit debug it takes me to this point in the code and highlights it

-------- Set dic = CreateObject("Scripting.Dictionary")

Would you be able to help?

tehneXus, this looks amazing. GREAT JOB on this! I really want to learn what you have done in this sheet, so I have a favor to ask. I have tried to step into the code (F8) and go step by step to see what happens, but there is a lot i cannot understand (a lot happening behind the scenes). Would it be possible for you to add notes to it all? I know this is asking a lot, but it would help me out tremendously to further my VBA knowledge, and be able to make updates to it. Thanks for all your help.

Also, the reason I ask this is because I'm wanting to add more functionality: If only Site is selected then it will breakdown every Style in that site. If Site and Brand is selected it will continue to operate as it does. If Site, Brand, and Gender is chosen then it will only breakdown off of these factors.

tehneXus, thank you for this, but it is taking me a while to understand. Would you be able to help with one final iteration? I've added in a Gender drop down box (please note, I have shifted down some of the criteria in the top left corner.) Just to clarify, when only Site is chosen then a breakdown happens on everything that matches the site. When Site and Brand are chosen then a breakdown happens where site and brand matches. When Site, Brand, and Gender are chosen then a breakdown happens where all three matches. Lastly, if Site and Gender are chosen then it would breakdown where those two match. I have been trying to make these changes on my own but I am running into many errors such as "Run-Time error "9": Subscript out of range" and many many others. Please let me know if this does not make sense, and I can try and elaborate more.

Test (2).xlsm

I am getting a "Compile error: Constant Expression Required" Error. It then goes on to highlight ".All" on line 41

Got that error fixed, now "Compile Error: Variable not defined" Line 87 "aOut"

"Run-time error '5': Invalid procedure call or argument"

Line 102: aDataout(i, j) = dic.Item(CStr(dKey)).Item(CStr(aDataout(1, j)))

Sorry, it seems to be working for me now. I think I had too many workbooks open. The only thing I did notice is that when Brand or Gender is not selected that information doesn't carry over to the breakdown. I would still like Brand and Gender to carry over to the breakdown and be grouped and summed together. When Site is selected you see all Brands, Styles, and Genders get grouped that fall under that site and summed on quantity, sales, and COGS. When Site and Brand is selected you see all Styles and Genders get grouped that fall under that site and brand summed on quantity, sales, and COGS. The same goes for Site, Brand, Gender and Site Gender. Does this make sense? Each filter is meant to provide another layer of detail. The way it does the breakdown when you have site, brand, and gender selected is how I would like it to function the whole way through.

Here is the updated code that I was trying to use to implement my above request. The issues I am having are:

1. When only site is selected: Brand and Gender show up as 0 on the breakdown
2. When Brand and Site is selected: Gender shows up as 0 on the breakdown
3. When Site and Gender is selected: Brand shows up as 0

How would I get these to actually display correctly?

Please Login or Register  to view this content.