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

1. ## 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.

2. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Hi,

check the file attached..Amount.xlsm

code:
``Please Login or Register  to view this content.``

3. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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.

4. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Sorry forgot that, please check this where the user can enter Date, Site and Brand Amount.xlsm

5. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Or with an pivot table.

See the attached file.

7. ## Re: Grouping, Summing, and Displaying data based off of user inputs

The code is based on 3 columns match.

8. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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.

9. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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.

10. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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.

12. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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.``

13. ## Re: Grouping, Summing, and Displaying data based off of user inputs

How would columns F and G be displayed when summing the qty, are we checking that these are the same?

output to E10 in activesheet:
``Please Login or Register  to view this content.``

14. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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?

15. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Hi,

could you please create a sample workbook and fill it with dummy data, I fear my example a few posts above is no longer valid

16. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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

18. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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?

19. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Hi,

looks like there is no dictionary object for mac, however I found a custom one, please test the attachment

20. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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.

21. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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.

22. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Hi,

commented code:
``Please Login or Register  to view this content.``
To get an idea of dictionaries check:
http://excelicious.wordpress.com/201...vs-collection/
http://www.techbookreport.com/tutori...ictionary.html

Please note: As there is no build in Dictionary Object for Mac a custom one was written as close as possible to the original one, however there might be differences in assigning values to items

23. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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

24. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Replace the entire code in module1 with the following:
``Please Login or Register  to view this content.``

25. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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

26. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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

27. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Just declare aOut as a variant in the module

28. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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

29. ## Re: Grouping, Summing, and Displaying data based off of user inputs

How did you fix the error you reported in post #25?

30. ## Re: Grouping, Summing, and Displaying data based off of user inputs

I just went back in and deleted all the code, and pasted in again and it worked. Then I added "Dim aOut() As Variant" to line 12 per your instructions in post #27.

31. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Hmm, I cannot reproduce that error.

What did you select for
Site - - - - - - - -
Brand - - - - - -
Gender - - - -

What is the value of "dKey" during that error?

What is the value of "aDataout(1, j)" during that error?

32. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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.

33. ## Re: Grouping, Summing, and Displaying data based off of user inputs

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.``

34. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Please check if this works on a mac: Test_rs.xlsm

35. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Works perfect on a PC, but I am receiving two errors on a Mac:

Error: 429 (ActiveX component can't create object) in Sub 'PrepareConnction' of Form 'frmKunden'.

When I click OK I get this next one.

Error: 429 (ActiveX component can't create object) in Sub 'Breakdown' Module 'Module2'.

36. ## Re: Grouping, Summing, and Displaying data based off of user inputs

Would be easier with ADO but here is a workaround with the custom dictionary again: Test_3.xlsm

code:
``Please Login or Register  to view this content.``

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

#### 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