Number of occurences for the most repeated value

[YasserKhalil]

Hello everybody

In my attachment I'm trying to find the most occurrences of all values and in adjacent column to count the number of occurrences ..
Expected output in Columns("I:J")
If possible I need to creat UDF function that do that task ...
That's extract unique values from the range("A2:D21") then sort the values according to the most occurrences ...
Hope it is clear
Thanks advaced

[JBeaucaire]

In J2, copied down:
=LARGE(G:G, ROW(A1))

In I2, copied down, this array formula:
=INDEX($F$1:$F$37, SMALL(IF($G$1:$G$37=J2, ROW($F$1:$F$37), ""), COUNTIF($J$1:$J2, $J2)))

[YasserKhalil]

Thanks a lot for help
In fact Columns G:H is just my try to solve the problem
I need to deal with the range("A2:D21") directly and get the results in Columns I:J
Thanks for swift reply

[JBeaucaire]

Why exactly is that needful?

[AB33]

No, function, but have a sub. Do you want it?

[YasserKhalil]

Ok Mr AB33 ...
It's my pleasure to receive your gift of course as I said "If possible" ..
Thanks advanced

[AB33]

Okay, try then

Note: Amended

[YasserKhalil]

Thank you very much for this unique help
It's working like charm
Regards

[karedog]

If you need the UDF anyway :

Please Login or Register  to view this content.

[YasserKhalil]

Great as usual Mr. Karedog
I tested it and it works in excellent way ..for both columns

Just if possible there are N/A error appears at the end of results .. Is it possible to show empty results instead of the N/A error?

[karedog]

This should do :

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very very much for all these great and huge help here and there and everywhere ...
You are really my dear friend(don't replace the letter "d" with "b") ..

[karedog]

You are welcome, I will "d"ear it in mind


Regards