Quote:
Originally Posted by carrolld2
Hi everyone, new to posting on this forum so thank you for your patience.
I have a spreadsheet which has data in 7 columns and around 100 rows. I want to be able to find what two numbers appear most frequent together on each of the rows. They don’t have to appear side by side they can appear anywhere on the row. It is kind of like the mode but instead of finding which number is most frequent it is to find what two numbers appear most frequent on each row?
Sorry if the explanation is not clear it’s hard to put in words.
I have searched all around but can find a solution, any help from you excel gods would be much appreciated. Below is a sample of the data....
Thank you.....
4 11 17 28 39 45 10
16 26 29 30 36 41 21
8 12 19 24 28 42 18
4 5 9 20 27 31 34
6 10 18 26 27 34 15
14 16 20 22 31 43 24
4 9 24 25 29 44 39
1 11 14 16 18 23 13
2 12 15 29 34 35 16
2 3 6 14 24 26 38
7 9 21 29 30 36 13
1 10 17 18 43 44 24
1 18 22 26 35 40 17
5 14 16 26 34 40 15
4 5 15 25 28 33 35
2 7 12 15 26 28 45
9 11 17 24 33 41 36
2 21 24 32 36 40 20
|
Okay so basically what you need to do is for each row you need to create a list of unique sets of two
The below will do this and spit it out into one column (there will be (#rows)*(#cols) *(#cols -1) entries - each set of two will be sorted in ascending order)
Then you can do a countif - so countif(J:J, J2)/2 - i have the output starting in row 2 so that an autofilter will slide straight in. the /2 in the countif is because there will be at least one duplicate for each entry, due to the nature of the VBA
NOTE - data is assumed to be in activesheet and starting in cell A1 with no jumps in data (blank cells)
I could have gone further and did the rest but didnt know what kind of an output you wanted.
So this will output the unique identifies for which you can insert a countif to find the most frequent ones
Sub Cycle()
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
last_row = ActiveCell.Row
last_col = ActiveCell.Column
row_out = 2
col_out = last_col + 3
For r = 1 To last_row
For j = 1 To last_col
y = 1
For x = 1 To last_col
If x = j Then
Else
If Cells(r, j) < Cells(r, x) Then
Cells(row_out, col_out) = Cells(r, j) & "_" & Cells(r, x)
Else
Cells(row_out, col_out) = Cells(r, x) & "_" & Cells(r, j)
End If
y = y + 1
row_out = row_out + 1
End If
Next x
Next j
Next r
End Sub