+ Reply to Thread
Results 1 to 36 of 36

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

  1. #1
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    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.
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #4
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    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. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

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

    Or with an pivot table.

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

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

    What about a date range instead of one single date?

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

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

    The code is based on 3 columns match.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #9
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    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. #10
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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.

  11. #11
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

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

    tehneXus I need your help.

  12. #12
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #13
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    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. #14
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #15
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    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. #16
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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

  17. #17
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

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

    Please check: Test.xlsm

  18. #18
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #19
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    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
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #21
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #22
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    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. #23
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #24
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    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. #25
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #26
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #27
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

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

    Just declare aOut as a variant in the module

  28. #28
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

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

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

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

  29. #29
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

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

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

  30. #30
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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.
    Last edited by AlphaSkidz; 09-30-2013 at 05:01 PM.

  31. #31
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    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. #32
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #33
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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. #34
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

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

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

  35. #35
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    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'.
    Last edited by AlphaSkidz; 10-02-2013 at 03:33 PM.

  36. #36
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    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.

+ 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. Replies: 5
    Last Post: 09-08-2013, 10:09 AM
  2. VBA to Turn Off Highlight When User Inputs Data
    By usumban in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2013, 05:16 AM
  3. Create a Square Shape Based on User Inputs
    By stensonnz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-15-2011, 11:13 PM
  4. Grouping Data and Summing numbers by row
    By bluegreen in forum Excel General
    Replies: 5
    Last Post: 10-25-2010, 12:22 AM
  5. Macro for 'n x n' Matrix formation based on user inputs
    By reachspk123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-03-2010, 03:31 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