carrolld2;1610900 said:

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