Combine MODE.MULT with additional function?

[Micobra]

Hi
I have a file containing of item numbers and all movements during a specific time frame. Below is an example from the file.

Item number Date Qty Unit
AB 2014-11-19 1 PC
CD 2015-02-16 7 PC
EF 2014-08-26 2 PC
EF 2014-08-26 3 PC
EF 2014-07-09 1 PC
EF 2014-07-09 1 PC
GH 2014-06-12 1 PC
IJ 2014-11-04 1 PC
IJ 2014-04-24 1 PC
IJ 2014-04-24 1 PC


I want to see the most common qty for each item number. I know that the function MODE.MULT can give me the most common qty for the whole file but how can I see it for each item number?

Thanks!

[DBY]

Hello
Maybe this array formula will do as you require using your sample data in A1:D11:

Please Login or Register  to view this content.

Arrays must be entered with Ctrl+Shift+Enter. You will see curly brackets {} around the formula if entered correctly. Item # would be the Item reference, typed directly or a cell reference. Also if there are no duplicate quantities it will return an #N/A error.

DBY