+ Reply to Thread
Results 1 to 7 of 7

Grouping data by sum, not sum based on group!

  1. #1
    Registered User
    Join Date
    07-24-2019
    Location
    Cleveland, OH
    MS-Off Ver
    2016
    Posts
    4

    Grouping data by sum, not sum based on group!

    Hello all! I have searched for a way to even ask this, but I keep finding answers on how to sum data by groups. That is not what I'm looking for. The best way to explain what I need is with a sample data set. Let's say I have some numbers in a range: 1,2,3,3,3,4,4,5,5,6,8,9,9. I am looking for a way to find out how many groups of 10 (or near 10) this set can make. Each number in the set would only be used once.

    1+9
    2+8
    3+3+4
    4+6
    5+5
    3
    9

    So this set would result in 7. There are 5 full 10s, and the remaining 9 and 3 can't be matched with anything else, so they each get their own group. I am self-taught intermediate in Excel, and not afraid to turn to VBA if needed, I just don't really know where to start here. I'm figuring it will involve an array formula, which I have not had the occasion to use often. This is an extremely simplified version of what I am actually trying to do, but if somebody can get me this far I can figure out how to apply it to my actual issue. Thank you in advance for the assist!

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Grouping data by sum, not sum based on group!

    Welcome to the forum.

    Not real clear on what you mean by "grouping" or what you want to do with them or where you want them put.

    I took this guess for groups =10.

    With the data source in A:C this in E2 filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    D
    E
    F
    G
    1
    2
    1
    9
    1
    9
    3
    2
    8
    2
    8
    4
    3
    3
    4
    3
    3
    4
    5
    4
    6
    4
    6
    6
    5
    5
    5
    5
    7
    3
    8
    9
    Last edited by FlameRetired; 07-24-2019 at 12:02 PM.
    Dave

  3. #3
    Registered User
    Join Date
    07-24-2019
    Location
    Cleveland, OH
    MS-Off Ver
    2016
    Posts
    4

    Re: Grouping data by sum, not sum based on group!

    Sorry it didn't seem clear, but thank you for the prompt response! I don't need anything moved or grouped. I don't need the numbers manipulated. What I need is a way to know how many "groups of 10" I can get out of that data set.
    Here's another example:
    I have a data set of 3,4,6,6,8,9,12
    How many 12's can I get?

    3+9 = 12
    4+8 = 12
    6+6 = 12
    12 = 12

    So the formula would return 4.

    If the set had an extra number, say: 3,4,6,6,8,9,9,12
    The formula would return 5. It found 4 groups that = exactly 12, plus one extra. The data set changes, so it probably will involve an array formula in some capacity. I hope this made it clearer!

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Grouping data by sum, not sum based on group!

    In your first example there are 13 numbers in the "pool". In the second there could be 7/8.

    I have an idea for a formula, but it is limited to a 20 number "pool". After that it requires multiple "helper" columns and the formula would have to be custom made.

    Will that work for you?

    If so please post a workbook with a representative sampling of what you will be working with.

    If you are not familiar with how to do this:

    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.

  5. #5
    Registered User
    Join Date
    07-24-2019
    Location
    Cleveland, OH
    MS-Off Ver
    2016
    Posts
    4

    Re: Grouping data by sum, not sum based on group!

    Yes, those are simplified examples. The actual data set im working with is 4 columns by 15 rows (always in the same place). It is dynamically filled, with the minimum being one row (4 data points) and the maximum being the entire thing. The numbers can be from 1 to 144. Rows will always be filled together, that is, if there is an entry in A1, there will also be entries in B1:D1. It puts a 0 if there is no data, but I can change that to "" if a 0 would interfere. I have attached a sterile example with no formulas. I'm quite good at reverse engineering, I just don't have enough experience to even know how to start on this problem. Thanks for your help, it's so cool to have somebody interested in solving this!
    Attached Files Attached Files

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Grouping data by sum, not sum based on group!

    I over committed in post #4. That last example would require calculating numbers larger than Excel can handle (by the method(s) I have in mind). In fact that would require larger than 1,152,921,504,606,850,000

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    There are only 17,179,869,184 cells in a sheet. So I think solution by formula is out of reach. Unfortunately I do not know enough VBA to be of help from here.
    Last edited by FlameRetired; 07-24-2019 at 06:15 PM.

  7. #7
    Registered User
    Join Date
    07-24-2019
    Location
    Cleveland, OH
    MS-Off Ver
    2016
    Posts
    4

    Re: Grouping data by sum, not sum based on group!

    Thank you for trying, at least.

+ 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. Trying to group an unusual grouping
    By 20GT in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-19-2018, 03:37 PM
  2. Replies: 1
    Last Post: 02-10-2014, 10:26 PM
  3. Replies: 2
    Last Post: 01-26-2011, 06:45 AM
  4. Grouping and identifying Min of each group
    By penfold in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-07-2010, 05:32 PM
  5. Grouping does not end. I want to start a new group
    By satswid in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2009, 11:31 PM
  6. [SOLVED] Grouping & Group Totals
    By pamarty in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-08-2006, 04:35 PM

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