sakendrick
New Member
- Joined
- Mar 21, 2007
- Messages
- 22
I've seen a few solutions for simple versions of the ask on different boards, but haven't found one that solves my need. I'm trying to find the most frequent combination of products owned across all our customers. But these are not unique combinations. For example 80% of my customers may have Product 1. 20 % of my customers may have product 1 and 2... but that includes some of those counted n the first 80%. 10% of my customers may have 1 & 2 and 3, which includes customers counted in the first two frequencies. Hope that makes sense. Added a mini sheet to show what my data looks like and a portion of the expected outcome. Hoping there's a way to use features or code this frequency analysis.
| Frequency Analysis.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| D | E | F | G | H | I | J | K | L | M | N | O | |||
| 7 | Product 1 | Product 2 | Product 3 | Product 4 | Product 5 | Product 6 | Product 7 | |||||||
| 8 | Cust 1 | 1 | 1 | Product 1 | 80% | |||||||||
| 9 | Cust 2 | 1 | 1 | 1 | 1 | Product 1 & 2 | 20% | |||||||
| 10 | Cust 3 | 1 | 1 | Product 1 & 2 & 3 | 10% | |||||||||
| 11 | Cust 4 | 1 | 1 | Product 1 & 3 | 33% | |||||||||
| 12 | Cust 5 | 1 | 1 | 1 | Product 2 & 3 | 20% | ||||||||
| 13 | Cust 6 | 1 | ...So on | |||||||||||
| 14 | Cust 7 | 1 | 1 | ...and so on | ||||||||||
| 15 | Cust 8 | 1 | 1 | |||||||||||
| 16 | Cust 9 | 1 | 1 | |||||||||||
| 17 | Cust 10 | 1 | 1 | |||||||||||
| 18 | 8 | 3 | 4 | 3 | 3 | 0 | 1 | |||||||
Sheet1 | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| O9 | O9 | =20% |
| E18:K18 | E18 | =COUNT(E8:E17) |
... 
