Listing most ffrequently occured sequence numbers

[Ram_6833]

Hi All.
I have been working on this for nearly a week now and about to lose the plot, hoping one of you genuises can help me.
I have bunch of 2 digit numbers say 100 rows and 5 colums, Is there a way of listing most frequent occuring sequence numbers with a formula? (I managed to work out single most occuring 5 numbers Using MODE(IF(ISERROR(MATCH)
I have posted a sample below hoping it'll help, as you can see numbers "8,13,18" highlighted in green.
Lets imagine "8,13,18" are the most sequence numbers in the whole table, is there a way of listing the amount of times these set of numbers occured in the data?
Thanks

List List List List List
2 14 26 33 37
7 10 18 26 37
1 8 13 18 42
2 5 12 15 34
8 11 13 18 29
19 30 32 33 35
4 6 15 20 24
6 8 11 13 18
3 20 22 24 28
6 7 20 26 31
7 33 34 37 45
1 3 8 13 18

[Sam Capricci]

if you already know the numbers you want to count you can use =SUM(COUNTIFS($A$2:$E$6,{8,13,18})) and adjust the range (that was my test range).

[WideBoyDixon]

Finding the most popular set is tricky and I don't think you can do it with a formula. I wrote a macro to find the most common set of [n] numbers where you can specify [n] like this:

Please Login or Register  to view this content.

Running the macro Test on your test set finds 4 occurrences of 8, 13, 18.

WBD

[Ram_6833]

I don't unfortunatelly but thank you. Im sure that will become handy too later on down the line

[Sam Capricci]

It does seem like you are asking two different questions, the first...

Is there a way of listing most frequent occuring sequence numbers with a formula?

And the other that I was going by...

Lets imagine "8,13,18" are the most sequence numbers in the whole table, is there a way of listing the amount of times these set of numbers occured in the data?

As for the first, WideBoyDixon gave you a macro.
Also for the first, I would've just put together a helper column with the min and max number in the group then all numbers between and a count of each then a rank then take the top 3 or 4 and use the sum(countif formula, if that is of any help.
But, to get better help maybe with a formula if WBD's macro isn't helpful, upload a sample workbook, click go advanced below the quick reply window, then midway down click on manage attachments, go to browse, upload, close window and submit to upload the workbook.

[Ram_6833]

Finding the most popular set is tricky and I don't think you can do it with a formula. I wrote a macro to find the most common set of [n] numbers where you can specify [n] like this:

Please Login or Register  to view this content.

Running the macro Test on your test set finds 4 occurrences of 8, 13, 18.

WBD

Thank you, im not the best with Macros but will definatelly give this ago, if i can master this it will save me lots of time thanks to you.

[Ram_6833]

It does seem like you are asking two different questions, the first...

And the other that I was going by...

As for the first, WideBoyDixon gave you a macro.
Also for the first, I would've just put together a helper column with the min and max number in the group then all numbers between and a count of each then a rank then take the top 3 or 4 and use the sum(countif formula, if that is of any help.
But, to get better help maybe with a formula if WBD's macro isn't helpful, upload a sample workbook, click go advanced below the quick reply window, then midway down click on manage attachments, go to browse, upload, close window and submit to upload the workbook.

I will give the macro ago soon if unsuccessful I will upload a sample version, Thank you.

[Ram_6833]

Hi Again.

SamboKid
Here I have uploaded a dummy sample of the workbook version.
If you could provide a Macro I would very much appricate it.
Thanks

[Sam Capricci]

Could you explain a bit more what exactly the results are you are looking for? WBD gave you a macro (I don't have macro skills so I can't help there) but it looked to me like in the first post you wanted some way to find the most frequent top numbers (how many is still a question) but it ALSO looked like you just wanted a formula to count 8, 13 and 18 which is what it looks like you've highlighted in the sample. AND if this is what you want, are you looking for a count of them COMBINED (like a count of all 8s and 13s and 18s) or INDIVIDUALLY (like a count of 8s, then a count of 13s and a count of 18s)?
If you want a count of those numbers that are the most frequent and a helper column is ok then that is an easy one.

[Ram_6833]

That Worked Perfectly,

Thank you so much

[Ram_6833]

Thank you, im not the best with Macros but will definatelly give this ago, if i can master this it will save me lots of time thanks to you.

Thats worked perfect thank you

[Sam Capricci]

I don't know exactly what your answer means so here is an example of how I'd go through it.
setting up a column of the range of numbers: you had from min of 1 to a max of 47 in the 5 columns.
I did a count for those numbers in the 5 columns, then ranked them without duplicates, then finally the sum(countif formula I gave you in post #2 but with the range from A3 to F197 and its results in cell K2.
the formulas were:
countif =COUNTIF($A$3:$F$197,H3) (the range of the numbers in your lists are in col H beginning in H3)
rank =RANK(I3,$I$3:$I$49,0)+COUNTIF($I$3:I3,I3)-1 - which treats gets rid of duplicates, so two with a count of 27 will be ranked as 15 and 16 instead of 15 and 15 with the next in the sequence being 17 (for example)
sumif/countif =SUM(COUNTIF($A$3:$F$197,{8,13,18}))
I also did the top 10 numbers shown by rank and their counts.
you can see in the attached the process.

I can't tell from your answers if it is solved or not.

[Ram_6833]

I don't know exactly what your answer means so here is an example of how I'd go through it.
setting up a column of the range of numbers: you had from min of 1 to a max of 47 in the 5 columns.
I did a count for those numbers in the 5 columns, then ranked them without duplicates, then finally the sum(countif formula I gave you in post #2 but with the range from A3 to F197 and its results in cell K2.
the formulas were:
countif =COUNTIF($A$3:$F$197,H3) (the range of the numbers in your lists are in col H beginning in H3)
rank =RANK(I3,$I$3:$I$49,0)+COUNTIF($I$3:I3,I3)-1 - which treats gets rid of duplicates, so two with a count of 27 will be ranked as 15 and 16 instead of 15 and 15 with the next in the sequence being 17 (for example)
sumif/countif =SUM(COUNTIF($A$3:$F$197,{8,13,18}))
I also did the top 10 numbers shown by rank and their counts.
you can see in the attached the process.

I can't tell from your answers if it is solved or not.

Hi and thank you for the example provided.

Apologies for any confusion, besically my question was which set of 3 numbers in sequence appeared most frequently (So if we imagine rows representing weeks and columns representing product number, my aim was to find 3 x top selling products over a year or so), in the sample provided they were "8,13,18" and simply wanted to find out how many times did these 3 x products appeared over 195 x weeks, I've applied the Macro provided above and it worked perfectly.

However what you provided me was going to be the next stage (breaking it down) so you have done me a big favour by providing me that.

Hope this is more clear and thanks for your help.

[Sam Capricci]

a couple suggestions Ram in case you come back with another issue.
1) don't quote posts, it is clutter and usually isn't necessary, if you want just refer to the post #.
2) if you are asked a question please address it, it likely is important to the person who asked it.
3) when you feel the issue has been resolved, please mark the post as solved using the thread tools dropdown at the top of the post so people will know, it doesn't mean someone won't come along and still work it, it just helps keep the site clean.
4) being somewhat new maybe you are not aware but you can thank any and ALL those who've tried to help you by clicking on the * Add Reputation at the bottom of their post to thank them, it is how we advance on this site.

Again, please feel free and welcome to come back and post questions OR at some point maybe you'll want to share your knowledge you've gained with someone who needs help here by solving their issue. That is how I got started.