+ Reply to Thread
Results 1 to 4 of 4

Generating list of possible combinations and getting number of occurrences

  1. #1
    Registered User
    Join Date
    06-12-2021
    Location
    Montreal, Canada
    MS-Off Ver
    Office 365
    Posts
    37

    Generating list of possible combinations and getting number of occurrences

    Good day,

    I am trying to list all the combination of items used with the associated counts for each combo occurence.

    I can use the filter to get the count for the different combination possibilty but it takes long when using the complete file. Concatenating all the item fields to a helper fields, then using a pivot table works but I am exploring a formula approach.

    Tx for your help in advance.

    Yves
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,894

    Re: Generating list of possible combinations and getting number of occurrences

    I hope I understand correctly. If so, this formula should give you all the combinations:

    In cell O4, try this:
    =UNIQUE(B2:J61)

    Then, to get the number of occurrences, in cell X4 try this:
    =COUNTA(FILTER(B2:J61,(B2:B61=O4)*(C2:C61=P4)*(D2:D61=Q4)*(E2:E61=R4)*(F2:F61=S4)*(G2:G61=T4)*(H2:H61=U4)*(I2:I61=V4)*(J2:J61=W4)))/9

    and copy it down as far as needed.
    I think there can be a better formula for the number of occurrences, and hopefully someone else can improve on this.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,277

    Re: Generating list of possible combinations and getting number of occurrences

    ARRAY formula in X4 then copied down

    =SUM(1*(MMULT(1*($B$2:$J$61=$O4:$W4),1*(TRANSPOSE(COLUMN($B$2:$J$2)>1)))=9))

    To enter ARRAY formula
    Copy and paste the formula in cell
    Press F2
    Press Ctrl+Shift+Enter together
    Excel covers the formula with {}.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,277

    Re: Generating list of possible combinations and getting number of occurrences

    Pl see file. Here is full answer.
    Helper column Z is used This Helps to avoid complicated formulas in all cells and making Excel slow. All formulas are very simple and fast.

    In O4 then copied down up to column W.

    Please Login or Register  to view this content.
    In X4 then copied down

    Please Login or Register  to view this content.
    In Z4 then copied down

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. Generating all possible combinations of 4 digit number via formula
    By Shawn7160 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 04-01-2021, 11:30 AM
  2. Generating all possible combinations from my list?
    By acmarston in forum Excel General
    Replies: 1
    Last Post: 10-25-2020, 06:05 AM
  3. Generating a list of combinations
    By tomhardy1491 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2015, 11:29 PM
  4. [SOLVED] Generating full list of scenarios (or combinations) of data in columns
    By cparker347 in forum Excel General
    Replies: 6
    Last Post: 07-28-2015, 10:28 AM
  5. generating 6 number lotto combinations
    By Hollywood691 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-19-2014, 02:04 AM
  6. [SOLVED] Generating a List of Possible Permutation Combinations
    By glennchung in forum Excel General
    Replies: 3
    Last Post: 05-12-2014, 06:39 PM
  7. Replies: 10
    Last Post: 01-24-2012, 08:58 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