Find and lists the 1st 2nd 3rd or nth most repeating number in a range

[Luisftv]

I found an array formula that allows me to determine and list the 1st, 2nd, 3rd, etc most repeating number in a range, a column in this case.

All I have to do is set in place the array formula on the first cell where I want the result and then drag down and it will automatically auto adjust to find the next consecutive most repeating number in the range.

However, the result given is not true at all.

The actual first most frequent number in the range is 2037, it repeats 70 times.

But the array formula picks 2038 as the first most repeating number, but it only repeats 66 times – four times less than the other number 2037. Obviously, something is wrong with the formula and I can’t figure out what.

The actual second most repeating number is 2034, it repeats 68 times. The actual third most repeating number is 2031, it repeats 67 times. But the array formula in place does not find these numbers at all.

I’m doing it this way (with an array formula) to avoid using VBA or a helper column.

In the attached file, column G (in dark gray) is where I need the results, where the array formula is in place. All the other columns and formulas are working fine.

Please help.

[Keebellah]

You can visualize it using Conditional formatting to show the top 10

[XLent]

would the below work for you ?

Please Login or Register  to view this content.

[Keebellah]

That won't work because there are too many and LARGE is not an option
See attached file

[Keebellah]

Yep, it works !!!
Re-attached my file with the new formula
Hope Luis is happy

[XLent]

I am bit confused by posts #4 & #5 -- the 2nd attachment (#5) is just the formula provided in post #3 -- not sure why this needed to be re-posted ?

the formula in post#3 converts the "frequency" # to millions and adds the associated value -- so most frequent is largest, but each value is unique
the AGGREGATE (w/large) will return the largest values from 1 to n
the outers MOD then strips out the value of interest (i.e. removes the frequency)

[Keebellah]

Okay, I just pasted it in my answer and updated the file.

[bakerman2]

Since this is the VBA-subforum just for fun.

Please Login or Register  to view this content.

[Luisftv]

The formula provided by XLent (post #3) and used in the file by Keebellah (post #5, after updating his file) works perfectly.

Yes, Keebellah, I'm super happy !!!

bakerman2, I do love VBA too! Can you please implement it in the file, please please? and attach it?

[bakerman2]

Done.

Happy to assist.

[Luisftv]

bakerman2,

Thank you. Works great.

Because it does not use any helper column (it's leaner), I'll be using the VBA solution.

If any one can find the way to use a formula (or array formula) that does not need any helper column, please let me know.

Everyone, have a very blessed day.

[Keebellah]

Post #5 with XLent's formula does not ned the helper column, if you read Bakerman2 remark he just did the VBA for fun

[Keebellah]

The same file, as @bakerman2 but no macros and a little conditional formatting and no helper columns and thanks to @XLent's formula

[Luisftv]

You are absolutely right Keebellah, my mistake.

I opened the file you provided in post #5 (your adaptation of XLent code) and I saw some helper columns and unintentionally took it for what was needed in for column G. I just opened it and erased all those helper columns and noticed that your adaptation in column G works flawlessly.

So this is awesome!!!

Again, I apologize for not noticing sooner. I just pulled a non-stop day at work without taking a lunch break, I'm not excusing myself but it's true I got distracted. Sorry.

Thank you both again. I not only got the formula I was needing but also VBA code which I love using too.

Oh, I just opened that last file you sent and it's flawless. I like those little flags!!!

Have a wonderful day and thank you again, and thanks again to XLent, and bakerman2 for the VBA code. I'm very grateful for having both options.

[Keebellah]

All the credit goes to @XLent for the formula, I'm no good at those things, my 'speciality' is VBA but I enjoy tweaking and playing around with whatever comes up in Excel files
Have a nice day too and ... Happy coding

[Rick Rothstein]

Since this is the VBA-subforum just for fun.

Please Login or Register  to view this content.

You do not actually have to test if the dictionary item exist in order to add it... the dictionary object will automatically add it if it doesn't exist. Given that, your If..Then..Else..EndIf block can be reduced to this single line of code...

Please Login or Register  to view this content.

[Luisftv]

Rick Rothstein,

Could you please provide the complete code, perhaps I did something wrong at my end.

With the code suggested by XLent, I get 57 repeating numbers, which is correct. When trying to shrink the code as you suggested I only get 20 repeating numbers and they are all wrong. I'm sure I did something wrong.

Thanks.


Never mind. I found my typo. Your suggestion works. Thank you so much.

[Rick Rothstein]

Rick Rothstein,

Could you please provide the complete code...

With the code suggested by XLent

In which message did XLent post VBA code?

[Luisftv]

bakerman2 provided the code in post #8.

But never mind, I found my typo. Here it is the bakerman2 code with your sugestion:

Please Login or Register  to view this content.

Thanks for the suggestion.

[Rick Rothstein]

XLent provided the code in post #8.

It was bakerman2 who provided the code in Message #8 (and that is who I replied to with my suggestion).

[Luisftv]

Yes, you are right. Apologies bakerman2. I corrected the mistake in that post.

Thank you so much again.

[XLent]

FWIW, for robustness, I would modify the formula, (per red):

Please Login or Register  to view this content.

[Keebellah]

In which message did XLent post VBA code?

XLent did NOT post any code, just a formula

[Luisftv]

Yes Keebellah, you are correct, and that has been noted and corrected as well. The VBA code was given by bakerman2. XLent gave the formula.

[Luisftv]

XLent,

The formula you provided in post #22 works flawlessly (yes, I removed the blank spaces as you noted). Thanks once more.