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

carrolld2

New Member
Joined
Apr 3, 2013
Messages
4
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..... :):):LOL:</SPAN></SPAN></SPAN></SPAN></SPAN>:LOL:
4111728394510
16262930364121
8121924284218
45920273134
6101826273415
14162022314324
492425294439
1111416182313
2121529343516
23614242638
792129303613
1101718434424
1182226354017
5141626344015
451525283335
271215262845
9111724334136
2212432364020

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

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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

-ABCDEFGH
1
2Combinations1111222334
3Input range2345345455
4123111
5254111
6145111
7135111
8243111
9
10Most121222112
Cell G4=IF(AND(COUNTIF($B4:$D4,G$2),COUNTIF($B4:$D4,G$3)),1,"")
Cell G10=COUNT(G4:G8)

<tbody>
</tbody>
 
Last edited:

carrolld2

New Member
Joined
Apr 3, 2013
Messages
4
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>
C:\Users\admin\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif
C:\Users\admin\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif
</SPAN>


4111728394510
16262930364121
8121926294218
45920273134
6101826293415
14162022314324
492425294439
1111416182313
2121529343516
23614242638
792129303613
1101718434424
1182226294017
5141626294015
451525283335
271217242845
9111724334136
2212432364020
11252728354518
1222253239401
8121724284129
4121323363821

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,722
Office Version
  1. 2010
Platform
  1. Windows
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?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

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]
            .Add Num, Array(Dn(, Ac), 1)
        [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
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
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.
 

carrolld2

New Member
Joined
Apr 3, 2013
Messages
4

ADVERTISEMENT

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>
C:\Users\carrold\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif
C:\Users\carrold\AppData\Local\Temp\msohtmlclip1\01\clip_image001.gif
</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>
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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]
            .Add Num, Array(Dn(, Ac), 1)
        [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
 

carrolld2

New Member
Joined
Apr 3, 2013
Messages
4
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!!!:LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

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
Top