Count maximum specific combinations between 12 columns

[sans.s]

Hello to everyone,

I was wondering if someone can help me with a problem I am facing. I would like to analyse a list of values and get the maximum apperance of 2,3,4,5,6,7 and 8 combinations. I have attached a sheet example for better understanding.

I have been trying to fnd a solution to this problem for a few days now which in turn led me to this forum.

Thank you in advance for your time,

Sans

[mikerickson]

Your example data is 9 columns, your "sample of actual data" is 12 columns.

Can we assume that we are always dealing with 12 or fewer columns?

[mdmackillop]

Welcome to VBAX
Give this a try
[VBA]Option Explicit
Option Base 1
Sub Test()
Dim r As Range, c As Range, tgt As Range
Dim i As Long, j As Long, Chk As Long, rw As Long, cl As Long
Dim arr(), fmla As String
Dim x, y, combo As Long
Dim Tck As String

Set r = Range("E14:E22")
Set tgt = Range("O14")
For i = 1 To 9

For j = 1 To 9
If j <> i Then
fmla = "=SUMPRODUCT(--(" & r.Offset(, i).Address & "=F9),--(" & r.Offset(, j).Address & "=F9))"
combo = Evaluate(fmla)
If combo > 1 Then
If InStr(1, tgt.Offset(, 2 * combo), i) = 0 Then
tgt.Offset(, 2 * combo) = tgt.Offset(, 2 * combo) & i & ","
tgt.Offset(, (2 * combo) + 1) = combo
End If
End If
End If
Next
Next
End Sub
[/VBA]

[mdmackillop]

Forgot so say: I put a Tick in F9 to use in the SumProduct function

[sans.s]

Thank you both very much for your replies.

Yes the columns in the actual data I will be analysing are 12. If it is possible for the code to function in either 12 or more columns it will be even better. If not, there is no problem. I can mix and match the columns later on.

Thank you,
Sans

[sans.s]

mdmackillop, thank you for your code, I have just tried it and I got a mix of correct and incorrect results. I am preparing now an example which I think is a little more clear on what the results should be. Thank you, Sans

[sans.s]

I am attaching another smaller example with only 4 columns which is a little more clearer.

Thank you for your time and help,
Sans

[mdmackillop]

Your sample is too simple and the logic is not clear to me.. Can you show results for 1,3, ticked, also 3,4 & 1,2,4

[sans.s]

Not a problem. I'll make a little more complex example now. Thank you for your help, Sans

[sans.s]

I am attaching another example which is a little more complex. Thank you again,Sans

[mdmackillop]

A bit rough, but give this a try

[sans.s]

Thank you.

I am attaching the same workbook back with some notes. I don't know if the notes convey correctly what I mean. Please let me know if I am being vague in any way.

Thank you again for your time and help,
Sans

[mdmackillop]

This corrects the totals found. There are multiple 5 & 6 tick results with maximum value. Which columns are to be listed?

[sans.s]

Thank you for your reply,
I am attaching back the workbook, with some notes. If for instance there is more than one max combination of 5 checkmarks, they can be listed under each other i.e.

1,2,4,9,10
1,2,8,9,12

Thank you,
Sans

[mdmackillop]

With each step this gets more complicated. This is beyond the scope I consider as free assistance as per our FAQ
Hopefully you can work out a solution.
Regards
MD

[sans.s]

That's ok, thank you very much for your time and effort,Sans

[mikerickson]

The array UDF in the attached will do what you want, albeit slowly.

This could be improved.