Most common value and it's most common value

[kskbeko]

Hi dear mentors
i want to do like this;
There is an item table with option no, items and its option values are not sorted,
i want to do from you can formula give me the 1st 2nd 3rd.... Most preffered item name and their most preffered option value like this
Formül.jpg
Thanks in advance and kindly regards of mine

[CARACALLA]

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Do not try to use the Paperclip icon, as this does not work on this forum.

[kskbeko]

i did as you want sir

[torachan]

Pivot table

[kskbeko]

Pivot table

Thank you sir but in your edit there give me sum of values, i don't want sum of options values i just want most common item and theirs most common option number, from most to less as i showed above. if i might use pivot table how to ?

[JeteMc]

Hello kskbeko and Welcome to Excel Forum.
This proposal employs two helper columns (C:D) which may be moved and/or hidden for aesthetic purposes.
Column C is populated using: =IF(COUNTIFS(A$2:A2,A2)=1,COUNTIFS(A$2:A$13,A2),"")
Column D is populated using: =COUNTIFS(A$2:A$13,A2,B$2:B$13,B2)
The most preferred item column is populated using: =INDEX(A$2:A$13,MATCH(LARGE(C$2:C$13,ROWS(A$1:A1)),C$2:C$13,0))
The most preferred option column is populated using: =INDEX(B$2:B$13,AGGREGATE(15,6,(ROW(B$2:B$13)-ROW(B$1))/(A$2:A$13=E16)/(D$2:D$13=AGGREGATE(14,6,D$2:D$13/(A$2:A$13=E16),1)),1))
Let us know if you have any questions.