Macro for Mode function(most frequent value)

Hello everyone,

I'm posting a problem I can't seem to solve.

I have a worksheet with 3 columns

It looks something like in the description below, but a lot more rows (around 3000-5000 rows) The number of codes is around 500 more or less, the number of prices is around 100 more or less, and the category number is around 30 pieces:

CODE PRICE CATEGORY
abcd 10 A
bgft 11 B
abcd 9 A
bgft 10 B
bgft 10 B
pqrst 7 C
pqrst 7 C
abcd 9 A
pqrst 6 C

So having this said I need a macro that will obtain me the most frequent price for every Code. It would be ideal if it keeps the original data, but this is not a mandatory request. In my worksheet let's say that some code repeats for 30-40 instances, and maybe others only repeat for 2-3 instances. The number of occurences for a code varies so it cannot be done manually, or it would take me ages to sort and apply Mode function.

I tried Pivot Table but pivot table doesn't support Mode or Median function

So is there anybody who can post a macro solution for this?

Thank you very much, in advance

Hello,
Thank you for your answer but still it is not exactly what i was looking for. Firstly your array formula is correct an functioning but in order to apply it for 3000 rows you have to click ctrl+shift+enter 3000 times. It is impossible to do that.
But there is a hope: Here is a macro that helps me expand the formula: http://www.wilmott.com/messageview.c...threadid=62734. And it is really working except that for singular records of a code it gives an error #N/A. This is not a problem, I can change the formula into a normal formula and expand it using auto-fill.

One single remark: after you run the macro You have to double click the right-bottom corner of the formula to expand and is done.

Thank you dillipandey and thank you