I have a sharepoint list with data as below.
ID EN DE FR Val
1 EN1 DE1 FR1 1
1 EN1 DE1 FR1 1
1 EN2 DE2 FR2 2
1 EN2 DE2 FR2 2
1 EN2 DE2 FR2 2
1 EN3 DE3 FR3 3
2 EN1 DE1 FR1 1
2 EN1 DE1 FR1 1
2 EN1 DE1 FR1 1
2 EN2 DE2 FR2 2
2 EN2 DE2 FR2 2
2 EN3 DE3 FR3 3
Now in the above list, i want to group by field Val and sort by the group by count which means the output should be as below for ID=1
Val2(3)
Val1(2)
Val3(1)
I tried using view, but could not find way to configure the way i want. So now i am thinking to use calculated field and that field will have the count as below and then group by that calculated field.
ID EN DE FR Val cal
1 EN1 DE1 FR1 1 2
1 EN1 DE1 FR1 1 2
1 EN2 DE2 FR2 2 3
1 EN2 DE2 FR2 2 3
1 EN2 DE2 FR2 2 3
1 EN3 DE3 FR3 3 1
2 EN1 DE1 FR1 1 3
2 EN1 DE1 FR1 1 3
2 EN1 DE1 FR1 1 3
2 EN2 DE2 FR2 2 2
2 EN2 DE2 FR2 2 2
2 EN3 DE3 FR3 3 1
Can you please help how i can achieve this. The main reason for this is , i want to show the items with max count of Val field in another list form.
Thanks in advance, Usha