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

carrolld2

New Member
Hi everyone, new to posting on this forum so thank you for your patience. </SPAN>
</SPAN>
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? </SPAN></SPAN>
Sorry if the explanation is not clear it’s hard to put in words.</SPAN></SPAN>

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....</SPAN></SPAN>

Thank you..... </SPAN></SPAN></SPAN></SPAN></SPAN>
 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

<COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY>
</TBODY>

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
This is nothing to do with excel but with mathematics and finding an eloquent way of finding this...

What is the possible range of the numbers 1-99? Integers only? Can one number appear twice in one row?

if the numbers are 1-99, and they can appear only once then we can have 4851 combinations of two numbers.

I guess you could do it in a semi-smart way in the spreadsheet. Below for range from 1-5 with input table of 3 columns

 - A B C D E F G H 1 2 Combinations 1 1 1 1 2 2 2 3 3 4 3 Input range 2 3 4 5 3 4 5 4 5 5 4 1 2 3 1 1 1 5 2 5 4 1 1 1 6 1 4 5 1 1 1 7 1 3 5 1 1 1 8 2 4 3 1 1 1 9 10 Most 1 2 1 2 2 2 1 1 2 Cell G4 =IF(AND(COUNTIF(\$B4:\$D4,G\$2),COUNTIF(\$B4:\$D4,G\$3)),1,"") Cell G10 =COUNT(G4:G8)

<tbody>
</tbody>

Last edited:
Hi sijpie</SPAN>, Many thanks for the reply. You are correct the numbers can only appear on each row once and the range would be between 1-45 but i am unclear on the example you proposed? I have listed below a sample of the data and highlighted examples of the results I would like to achieve. Marked in red are the numbers 26 & 29 and in gold are 17 & 24 the red numbers appear more frequent i.e. 5 times in comparison to the 4 of the gold. I wanted Excel to be able to identify the two numbers that appear on the same row most often. I don’t want to search for any particular numbers just to be told what is basically the mode but instead of one number I wanted it for two?

Hope the above is clear it is hard to explain, and thank you for taking the time to provide a solution.... </SPAN>
</SPAN>

 4 11 17 28 39 45 10 16 26 29 30 36 41 21 8 12 19 26 29 42 18 4 5 9 20 27 31 34 6 10 18 26 29 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 29 40 17 5 14 16 26 29 40 15 4 5 15 25 28 33 35 2 7 12 17 24 28 45 9 11 17 24 33 41 36 2 21 24 32 36 40 20 11 25 27 28 35 45 18 12 22 25 32 39 40 1 8 12 17 24 28 41 29 4 12 13 23 36 38 21

<COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY>
</TBODY>

Do the numbers have to be in the same order? That is, does 29-26 on a row match 26-29 on a different row?

Try this:-
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG07Apr56
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]For[/COLOR] Ac = 1 To 6
Num = Dn(, Ac) & "," & Dn(, Ac + 1)
[COLOR="Navy"]If[/COLOR] Not .Exists(Num) [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Else[/COLOR]
Q = .Item(Num)
Q(1) = Q(1) + 1
[COLOR="Navy"]Set[/COLOR] Q(0) = Union(Q(0), Dn(, Ac))
.Item(Num) = Q
oMax = Application.Max(oMax, Q(1))
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Item(K)(0).Areas
[COLOR="Navy"]If[/COLOR] .Item(K)(1) = oMax [COLOR="Navy"]Then[/COLOR]
Temp = K
R.Resize(, 2).Interior.ColorIndex = 6
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
MsgBox "Max Dup numbers = " & Temp & " (" & oMax & ")"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

My formula and table will work exactly for what you want. If you don't want to use macros, but work in formulas see below, else try the macro given by Mick

To use it create a table such as in my example to the right of your table and try to follow the logic for the combinations. So the first row will contain 44 x1, with 2 to 45 on the 2nd row below. Then 43 x 2 with 3 to 45 on the 2nd row, then 42 x3 with 4 to 45 on 2nd row, etc till 1x 44 with 45 on 2nd row.

Then use the formulas in my example (adjust the first to the correct column/row for your table) and copy across and down. Then the sum formula on the bottom.

Hi MickG,
That macro was perfect it identified the numbers as I needed. I am not to sure if it is possible to identify say the first 3 sets of most common numbers? If it is a major hassle you can leave it but just wondering. Again thanks for the help you saved me a lot of work.....</SPAN>
</SPAN>

sijpie, I wanted to thank you too for your response, which was very intuitive but the macro seemed to work for a simpler excel user like me..</SPAN>

Try this:-
Its not perfect, You could get a slighly muddled msg depending on the count, but in the main, you should get what you want.
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG10Apr08
[COLOR="Navy"]Dim[/COLOR] rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Num         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Set[/COLOR] rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
rng.Resize(, 7).Interior.ColorIndex = xlNone
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] rng
[COLOR="Navy"]For[/COLOR] Ac = 1 To 6
Num = Dn(, Ac) & "," & Dn(, Ac + 1)
[COLOR="Navy"]If[/COLOR] Not .Exists(Num) [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Else[/COLOR]
Q = .Item(Num)
Q(1) = Q(1) + 1
[COLOR="Navy"]Set[/COLOR] Q(0) = Union(Q(0), Dn(, Ac))
.Item(Num) = Q

[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] R       [COLOR="Navy"]As[/COLOR] Range
ReDim ray(1 To .Count)
ReDim temp(1 To 3, 1 To 2)

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
c = c + 1
ray(c) = .Item(K)(1)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]For[/COLOR] n = 1 To 3
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
[COLOR="Navy"]If[/COLOR] .Item(K)(1) = Application.Large(ray, n) [COLOR="Navy"]Then[/COLOR]
temp(n, 1) = .Item(K)(1)
temp(n, 2) = temp(n, 2) & " - " & K
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Item(K)(0).Areas
R.Resize(, 2).Interior.ColorIndex = n + 5
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] With

MsgBox "1st Dup Count = " & temp(1, 1) & " (" & Mid(temp(1, 2), 4) & ") Yellow" & Chr(10) & _
"2nd Dup Count = " & temp(2, 1) & " (" & Mid(temp(2, 2), 4) & ") Mauve" & Chr(10) & _
"3rd Dup Count = " & temp(3, 1) & " (" & Mid(temp(3, 2), 4) & ") Blue"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

Hi Mick,
I have one final question and then I will stop annoying you Is it possible to change that Macro so that it finds the 3 numbers that occur most often instead of the 2 numbers that occur most often together?

Thank you again for your help!!!

Replies
10
Views
331
Replies
4
Views
259
Replies
3
Views
171
Replies
4
Views
151
Replies
0
Views
160

Forum statistics

1,203,491
Messages
6,055,727
Members
444,814
Latest member
AutomateDifficulty

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.

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

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