# Generating list of possible combinations and getting number of occurrences

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

Yves  Register To Reply

2. ## 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.  Register To Reply

3. ## 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 {}.  Register To Reply

4. ## 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.``  Register To Reply