Help!! find two number recurring numbers in row??

[carrolld2]

Hi everyone, new to posting on this forum so thank you for you
patience.

I have a spreadsheet which has data in 7 columns and around 100 rows.
want to be able to find what two numbers appear most frequent togethe
on each of the rows. They don’t have to appear side by side they ca
appear anywhere on the row. It is kind of like the mode but instead o
finding which number is most frequent it is to find what two number
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 yo
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 2

 

[shanermuls]

Hi everyone, new to posting on this forum so thank you for you
patience.

I have a spreadsheet which has data in 7 columns and around 100 rows.
want to be able to find what two numbers appear most frequent togethe
on each of the rows. They don’t have to appear side by side the
can appear anywhere on the row. It is kind of like the mode but instea
of finding which number is most frequent it is to find what two number
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 yo
excel gods would be much appreciated. Below is a sample of the data...

Thank you.....
4 11 17 28 39 45 1
16 26 29 30 36 41 2
8 12 19 24 28 42 1
4 5 9 20 27 31 3
6 10 18 26 27 34 1
14 16 20 22 31 43 2
4 9 24 25 29 44 3
1 11 14 16 18 23 1
2 12 15 29 34 35 1
2 3 6 14 24 26 3
7 9 21 29 30 36 1
1 10 17 18 43 44 2
1 18 22 26 35 40 1
5 14 16 26 34 40 1
4 5 15 25 28 33 3
2 7 12 15 26 28 4
9 11 17 24 33 41 3
2 21 24 32 36 40 2

Okay so basically what you need to do is for each row you need to creat
a list of unique sets of tw

The below will do this and spit it out into one column (there will b
(#rows)*(#cols) *(#cols -1) entries - each set of two will be sorted i
ascending order

Then you can do a countif - so countif(J:J, J2)/2 - i have the outpu
starting in row 2 so that an autofilter will slide straight in. the /
in the countif is because there will be at least one duplicate for eac
entry, due to the nature of the VB

NOTE - data is assumed to be in activesheet and starting in cell A1 wit
no jumps in data (blank cells

I could have gone further and did the rest but didnt know what kind o
an output you wanted

So this will output the unique identifies for which you can insert
countif to find the most frequent one

Sub Cycle(

Range("A1").Selec
Selection.End(xlDown).Selec
Selection.End(xlToRight).Selec
last_row = ActiveCell.Ro
last_col = ActiveCell.Colum
row_out =
col_out = last_col +

For r = 1 To last_ro
For j = 1 To last_co
y =
For x = 1 To last_co

If x = j The
Els
If Cells(r, j) < Cells(r, x) The

Cells(row_out, col_out) = Cells(r, j) & "_"
Cells(r, x
Els
Cells(row_out, col_out) = Cells(r, x) & "_"
Cells(r, j
End I

y = y +
row_out = row_out +
End I
Next


Next

Next


End Su