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
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.
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 {}.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
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.
In X4 then copied down
Please Login or Register to view this content.
In Z4 then copied downPlease Login or Register to view this content.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks