How do I find the frequency of 3 numbers in a 7 column row

MyHeadAches

New Member
Joined
Apr 28, 2013
Messages
4
Hello all,

I am having some difficulty in trying to get my head around a problem I am having in analyzing data in Excel and was hoping one of you wizards may be able to help me.

I have approximately 2000 rows of numerical data in 7 columns. I need to find the most frequent three numbers that occur on a row in any of the 7 columns.

Below is an example of what I mean:

3, 43 and 44 appear together in two rows but not in any particular order, and;
2, 17 and 39 appear three times together in a row but again not in any particular order.

The numbers do not need to be in any order or pattern just on the same row as each other.

1222233645461
133743444814
8162527283432
8112732333544
212172533391
15283243444520
6182025353631
1826433544473
2173639424841
24519202721
141637474927
1719394144452

<tbody>
</tbody>

<tbody>
</tbody>


I think a macro might be able to obtain the results I need but it is well beyond my understanding - Does it sound like something that could be done? I'd very much appreciate some assistance if it's possible.

Thanks in advance,

MyHeadAches!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I think this would require VBA.

My approach would be to create an array (or collection) of 18424 (=combin(49,3)) integers indexed by the combination number. Increment the array entry for each of the 20 3-number combinations formed in each row, and then find the one or more entries having the maximum value in the array.
 
Upvote 0
Select the range you want to analyse (or tweak the commented-out line early in the macro) and run this macro:
Code:
Sub blah()
'Range("A1:G12").Select
Selection.Copy
Set newsht = Sheets.Add
newsht.Paste
With Selection
  .Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
  xx = .Value
End With
Application.DisplayAlerts = False: newsht.Delete: Application.DisplayAlerts = True
Set Dict = CreateObject("Scripting.Dictionary")
For rw = 1 To UBound(xx)
  For i = 1 To UBound(xx, 2) - 2
    For j = i + 1 To UBound(xx, 2) - 1
      For k = j + 1 To UBound(xx, 2)
        myStr = Join(Array(xx(rw, i), xx(rw, j), xx(rw, k)), ",")
        If Dict.Exists(myStr) Then
          Dict(myStr) = Dict(myStr) + 1
        Else
          Dict.Add myStr, 1
        End If
Next k, j, i, rw
myMax = Application.Max(Dict.items)
If myMax > 1 Then
  For Each thing In Dict
    If Dict(thing) = myMax Then MsgBox "The combo " & thing & " occurs " & myMax & " times."
  Next thing
Else
  MsgBox "No 3 numbers appear more than once"
End If
End Sub
 
Upvote 0
SHG,

Many thanks for replying.
I will try your suggestion after I finish work in a few hours when I'm back home.

P45cal,

Many thanks for responding too and my apologies for the delay in replying - I'm in the UK and have been asleep or at work since I posted. I should've said that in the post rather than keep you waiting so again, I apologise to you.

I have looked at the macro and tested it on a sample spreadsheet and it's exactly what I am after so thank you so much. I have literally no idea how a person could come up with that code but that is why I used the term 'wizards' in my original post!


Thanks again to you SHG and to you P45cal for replying and your kind assistance.
 
Upvote 0
@p45: Change G1 to 39, and I don't believe the code detects the 2,17,39 combination in the last row. The code sorts the data only by the first row, which happens to put it in the correct order.
 
Upvote 0
Yes, shg, you're quite right! Why ever did I think that each row would be sorted independently with my code?!
So to correct it involves a simplification of the code, no copying to a new sheet, no sorting (though one line is more complex):
Code:
Sub blah2()
'Range("A1:G12").Select
xx = Selection.Value
Set Dict = CreateObject("Scripting.Dictionary")
For rw = 1 To UBound(xx)
  For i = 1 To UBound(xx, 2) - 2
    For j = i + 1 To UBound(xx, 2) - 1
      For k = j + 1 To UBound(xx, 2)
        myStr = Join(Array(Application.Max(xx(rw, i), xx(rw, j), xx(rw, k)), Application.Median(xx(rw, i), xx(rw, j), xx(rw, k)), Application.Min(xx(rw, i), xx(rw, j), xx(rw, k))), ",")
        If Dict.Exists(myStr) Then
          Dict(myStr) = Dict(myStr) + 1
        Else
          Dict.Add myStr, 1
        End If
Next k, j, i, rw
myMax = Application.Max(Dict.items)
If myMax > 1 Then
  For Each thing In Dict
    If Dict(thing) = myMax Then MsgBox "The combo " & thing & " occurs " & myMax & " times."
  Next thing
Else
  MsgBox "No 3 numbers appear more than once"
End If
End Sub
 
Last edited:
Upvote 0
Seriously? You guys really know your stuff!

I have run a test on my data and it seemed to be working fine... although it took me a few tries before I figured out that I needed to highlight the data first! I feel so stupid that I can't manage that and you can write and understand code! haha

So my understanding now, correct me if i'm wrong, your new code will find the most frequently occuring three numbers in a row but in any order that they appear? But before, the code was looking for the numbers anywhere on a row but in chronological order (or how they first appear on the spreadsheet)?

The new code does seem to have found two more examples on my data so it seems to be working! :)

Can I ask, what code language is it written in? I tried to learn Java once but it was beyond my understanding so would it be easier to learn a simpler language first for the basics? Sorry, I know that's a little off topic but i'm intrigued how you would first approach learning something so complicated to a beginner!
 
Upvote 0
So my understanding now, correct me if i'm wrong, your new code will find the most frequently occuring three numbers in a row but in any order that they appear? But before, the code was looking for the numbers anywhere on a row but in chronological order (or how they first appear on the spreadsheet)?
My first offering was supposed to handle data in any order it appeared on each row. The whole business of copying the data to a new sheet, sorting it there, then deleting it later was so as to preserve your original data as it was. I thought I was sorting all the rows; I wasn't. Only the top row. Now I only do the 'sorting' on three numbers at a time - it's not really sorting - I take the max, median and min values of the three numbers in that order, and use that for counting occurrences of those three numbers.

Can I ask, what code language is it written in? I tried to learn Java once but it was beyond my understanding so would it be easier to learn a simpler language first for the basics? Sorry, I know that's a little off topic but i'm intrigued how you would first approach learning something so complicated to a beginner!
This is VBA, Visual Basic for Applications. It's a flavour of Visual Basic, built-in to many of the MicroSoft Office Applications.
 
Upvote 0
Well it works a treat so thank you very much indeed. I have tested it by deleting the most frequent rows and it then finds the next most frequent so it completely suits my requirements.

I have about 2000 rows of data so going through manually would've been an almost impossible task and absolutely tedious too!

I will look into VBA and try to learn some simple commands then as it could be a good starting point for programming.

Anyway, thank you very much again - It really is appreciated :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top