PDA

View Full Version : Count maximum specific combinations between 12 columns



sans.s
11-13-2011, 06:29 AM
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
11-13-2011, 07:58 AM
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
11-13-2011, 08:06 AM
Welcome to VBAX
Give this a try
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

mdmackillop
11-13-2011, 08:10 AM
Forgot so say: I put a Tick in F9 to use in the SumProduct function

sans.s
11-13-2011, 08:20 AM
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
11-13-2011, 08:48 AM
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
11-13-2011, 09:22 AM
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
11-13-2011, 09:46 AM
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
11-13-2011, 09:52 AM
Not a problem. I'll make a little more complex example now. Thank you for your help, Sans

sans.s
11-13-2011, 10:18 AM
I am attaching another example which is a little more complex. Thank you again,Sans

mdmackillop
11-13-2011, 01:46 PM
A bit rough, but give this a try

sans.s
11-13-2011, 03:13 PM
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
11-13-2011, 05:18 PM
This corrects the totals found. There are multiple 5 & 6 tick results with maximum value. Which columns are to be listed?

sans.s
11-14-2011, 12:59 AM
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
11-14-2011, 02:02 AM
With each step this gets more complicated. This is beyond the scope I consider as free assistance as per our FAQ (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_posthelp_faq_item)
Hopefully you can work out a solution.
Regards
MD

sans.s
11-14-2011, 02:23 AM
That's ok, thank you very much for your time and effort,Sans

mikerickson
11-14-2011, 07:11 PM
The array UDF in the attached will do what you want, albeit slowly.

This could be improved.