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

C

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..... :):):laugh:</SPAN></SPAN></SPAN></SPAN></SPAN>:laugh:
[TABLE="width: 336"]
<COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]39[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]45[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]36[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]41[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]21[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]42[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]18[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]31[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]34[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]31[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]43[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]44[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]39[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]23[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]35[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]38[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]36[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]43[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]44[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]35[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]17[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]33[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]35[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]45[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]33[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]41[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]36[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]36[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]20[/TD]
[/TR]
</TBODY>[/TABLE]
 
Sort by date Sort by votes
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

[TABLE="width: 1088"]
<tbody>[TR]
[TD="class: xl65, width: 64"]-[/TD]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl68"]Combinations[/TD]
[TD="class: xl67, align: right"]1[/TD]
[TD="class: xl67, align: right"]1[/TD]
[TD="class: xl67, align: right"]1[/TD]
[TD="class: xl67, align: right"]1[/TD]
[TD="class: xl67, align: right"]2[/TD]
[TD="class: xl67, align: right"]2[/TD]
[TD="class: xl67, align: right"]2[/TD]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl67, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]Input range[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl67, align: right"]2[/TD]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl67, align: right"]4[/TD]
[TD="class: xl67, align: right"]5[/TD]
[TD="class: xl67, align: right"]3[/TD]
[TD="class: xl67, align: right"]4[/TD]
[TD="class: xl67, align: right"]5[/TD]
[TD="class: xl67, align: right"]4[/TD]
[TD="class: xl67, align: right"]5[/TD]
[TD="class: xl67, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Most[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cell G4[/TD]
[TD="colspan: 6"]=IF(AND(COUNTIF($B4:$D4,G$2),COUNTIF($B4:$D4,G$3)),1,"")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cell G10[/TD]
[TD="colspan: 2"]=COUNT(G4:G8)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
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>


[TABLE="width: 336"]
<COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]39[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]45[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl66, width: 64, bgcolor: red, align: right"]26[/TD]
[TD="class: xl66, width: 64, bgcolor: red, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]36[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]41[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]21[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]19[/TD]
[TD="class: xl66, width: 64, bgcolor: red, align: right"]26[/TD]
[TD="class: xl66, width: 64, bgcolor: red, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]42[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]18[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]31[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]34[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl66, width: 64, bgcolor: red, align: right"]26[/TD]
[TD="class: xl66, width: 64, bgcolor: red, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]31[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]43[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]44[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]39[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]23[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]34[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]35[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]38[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]30[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]36[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]13[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]43[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]44[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl66, width: 64, bgcolor: red, align: right"]26[/TD]
[TD="class: xl66, width: 64, bgcolor: red, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]17[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]14[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl66, width: 64, bgcolor: red, align: right"]26[/TD]
[TD="class: xl66, width: 64, bgcolor: red, align: right"]29[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]15[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]33[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]35[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl67, width: 64, bgcolor: #ffc000, align: right"]17[/TD]
[TD="class: xl67, width: 64, bgcolor: #ffc000, align: right"]24[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]45[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl67, width: 64, bgcolor: #ffc000, align: right"]17[/TD]
[TD="class: xl67, width: 64, bgcolor: #ffc000, align: right"]24[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]33[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]41[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]36[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]24[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]36[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]27[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]35[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]45[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]18[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]22[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]32[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]39[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]40[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl67, width: 64, bgcolor: #ffc000, align: right"]17[/TD]
[TD="class: xl67, width: 64, bgcolor: #ffc000, align: right"]24[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]28[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]41[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]29[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]13[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]23[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]36[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]38[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]21[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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!!!:laugh:
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Dynamic arrays, circular arguments and all I want to due is increment a previous number
Replies
3
Views
166
djclements
djclements
E
  • Question Question
counting duplicates error
Replies
1
Views
179
jasonb75
J
S
  • Solved
Individual Ranking
2
Replies
11
Views
510
sksanjeev786
S
K
  • Question Question
conditional formatting question
Replies
2
Views
686
kerm007
K
B
  • Solved
timestamp listing to show more than >19hrs away per day?
Replies
9
Views
239
Boboka
B

Share this page

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
Back
Top