Frequency Analysis for all Combinations of Data Across Multiple Columns

[sakendrick]

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)

 

[lrobbo314]

Let me know if this is what you are looking for.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Customer	Product 1	Product 2	Product 3	Product 4	Product 5	Product 6	Product 7					Combo	Count	Percent
2	Cust 1	1		1									1	8	80%
3	Cust 2	1	1	1		1							2	3	30%
4	Cust 3	1	1										3	4	40%
5	Cust 4	1				1							4	3	30%
6	Cust 5	1		1	1								5	3	30%
7	Cust 6	1											7	1	10%
8	Cust 7		1	1									1,2	2	20%
9	Cust 8					1		1					1,3	3	30%
10	Cust 9	1			1								1,4	3	30%
11	Cust 10	1			1								1,5	2	20%
12													2,3	2	20%
13													2,5	1	10%
14													3,4	1	10%
15													3,5	1	10%
16													5,7	1	10%
17													1,2,3	1	10%
18													1,2,5	1	10%
19													1,3,4	1	10%
20													1,3,5	1	10%
21													2,3,5	1	10%
22													1,2,3,5	1	10%
Sheet1

Sub COMBOX()
Dim AL As Object:       Set AL = CreateObject("System.Collections.ArrayList")
Dim SD As Object:       Set SD = CreateObject("Scripting.Dictionary")
Dim Data() As Variant:  Data = Range("B2:H" & Range("A" & Rows.Count).End(xlUp).Row).Value2
Dim Grp As Integer:     Grp = 7
Dim Tot As Integer:     Tot = 7
Dim b As Boolean:       b = True
Dim i As Integer
Dim r As Range

For i = 1 To Grp
    Main AL, i, Tot
Next i

For Each AI In AL
    SP = Split(AI, ",")
    For ro = 1 To UBound(Data)
        b = True
        For Each s In SP
            If Data(ro, s) <> 1 Then b = False
        Next s
        If b Then SD(AI) = SD(AI) + 1
    Next ro
Next AI

With Range("M1:O1")
    .Value = Array("Combo", "Count", "Percent")
    .Font.Bold = True
End With
Set r = Range("M2").Resize(SD.Count)
r.Value2 = Application.Transpose(SD.keys)
r.Offset(, 1).Value2 = Application.Transpose(SD.items)
With r.Offset(, 2)
    .Formula2R1C1 = "=RC[-1]/10"
    .Value = .Value2
    .NumberFormat = "#,##0%"
End With

End Sub

Sub Main(AL As Object, Grp As Integer, Tot As Integer)
Dim AR() As Variant:        AR = Evaluate("TRANSPOSE(INDEX(ROW(1:" & Tot & "),))")

Combo AR, Grp, 1, 0, "", AL
End Sub

Sub Combo(AR() As Variant, Grp As Integer, IDX As Integer, Depth As Integer, Buffer As String, AL As Object)
Dim Prefix As String

For i = IDX To UBound(AR)
    If Buffer = vbNullString Then
        Prefix = AR(i)
    Else
        Prefix = Join(Array(Buffer, AR(i)), ",")
    End If
    If Depth + 1 = Grp Then
        AL.Add Prefix
    Else
        Combo AR, Grp, i + 1, Depth + 1, Prefix, AL
    End If
Next i
End Sub

 

[EXCEL MAX]

Hello Kendrick, I don't think it's possible to do that only with formulas.
You can think about new approach. Here is how I see analyse.
If you want to analyze products they should be in the rows and customers in the columns.
After that you should select in the filter products you want to analyse.
With VBA code you can count only visible values (xlVisible) in the each column.
Now you can also with VBA code count columns with same number of values as number of the visible rows.
In the end, this number divide with number of all products in the table and format as percent.

 

[sakendrick]

@lrobbo314 the resulting data looks correct - let me see if I can manipulate it for my real data... I"m assuming this goes in code for the page... weird question, but how do I run it? I'm relatively new to using VBA, and normally trigger the routine off of a cell change.

 

[sakendrick]

so I tried pasting in my sample file that I shared here, but when I run it I get an automation error... no other information ?‍

 

[lrobbo314]

Get into the VBA editor and go to insert, then module. Paste the code in the standard module and run it. The data I pasted didn't have the total row like the original did, so maybe get rid of that and try. Give that a shot and get back to me. Automation error sounds like it might be that you are missing .net. but let me know if you still get the error.

 

[sakendrick]

yep - I had tried all that - remove the total row, inserted a module and pasted code in there. Seems to choke on the first Set AL=.. Appreciate the help.

 

[lrobbo314]

Yeah, that's because it's missing .net. you can try installing it. Otherwise, I can adapt it tomorrow to use a dictionary instead of an arraylist.

 

[sakendrick]

I had another system I could test on that I guess had the right .net framework as it works. Now to customize this for a different spreadsheet... what are the items I need to edit?
- I see two integers set to a value of 7... is that the number of columns and should be adjusted?
- I see references to M1:O1 and M2... is that the output location?

anything else?
Thanks again - this is great.

 

[sakendrick]

and the Data variable Range...