Finding and arranging rows then displaying their frequency in descending order:

1. Finding and arranging rows then displaying their frequency in descending order:

Hi,

There are two Groups of Data:

GROUP A

From A TO D there are initial values.

``Please Login or Register  to view this content.``
From E TO H there are values which have occured more than once.

``Please Login or Register  to view this content.``
For Group A there are 2 tasks to be completed.

The first task is to arrange the rows in E TO H according to the number of times it has occured at A TO D in descending order as followes:

``Please Login or Register  to view this content.``
As "43,5,40,18" has occurred the maximum number of times which is 4 times so it is on top then follows "19,18,16,12" & "6,7,12,41 as they have occurred 3 times each, then there is "4,9,30,18"
at the bottom because it has occurred the minimum number of times which is 2 times.

The second task is to display the frequency of each rows From I TO L at M as follows:

``Please Login or Register  to view this content.``
AS "43,5,40,18" has occurred 4 times in A TO D there is 4 at M1, "19,18,16,12" and "6,7,12,41" has occurred 3 times each in A TO D there is 3 at M2 and M3 respectively, then "4,9,30,18" has
occurred 2 times in A TO D there is 2 at M4.

GROUP B

From N TO P there are initial values.

``Please Login or Register  to view this content.``
From Q TO S there are values which have occured more than once.

``Please Login or Register  to view this content.``
For Group B there are 2 tasks to be completed same as in Group A.

The first task is to arrange the rows in Q TO S according to the number of times it has occurred at N TO P in descending order as follows:

``Please Login or Register  to view this content.``
As "43,5,40" has occurred the maximum number of times which is 4 times so it is on top then follows "19,18,16" & "6,7,12 as they have occurred 3 times each, then there is "4,9,30"
at the bottom because it has occured the minimum number of times which is 2 times.

The second task is to display the frequency of each rows From T TO V at W as follows:

``Please Login or Register  to view this content.``
AS "43,5,40" has occurred 4 times in N TO P there is 4 at W1, "19,18,16" and "6,7,12" has occured 3 times each in N TO P so there is 3 at W2 and W3 respectively, then "4,9,30" has
occured 2 times in N TO P there is 2 at W4.

2. Re: Finding and arranging rows then displaying their frequency in descending order:

I think I found a solution for you, but it requires you to add a row of headers above all of your data. Once that's done, use the formula below in I2. It should be array-entered (confirmed with ctrl + shift + enter instead of enter), then filled right through L2 and down:

=INDEX(A\$2:A\$15,MIN(IF(IF(COUNTIFS(\$I\$1:\$I1,\$A\$2:\$A\$15,\$J\$1:\$J1,\$B\$2:\$B\$15,\$K\$1:\$K1,\$C\$2:\$C\$15,\$L\$1:\$L1,\$D\$2:\$D\$15)=0,COUNTIFS(\$A\$2:\$A\$15,\$A\$2:\$A\$15,\$B\$2:\$B\$15,\$B\$2:\$B\$15,\$C\$2:\$C\$15,\$C\$2:\$C\$15,\$D\$2:\$D\$15,\$D\$2:\$D\$15))=LARGE(IF(COUNTIFS(\$I\$1:\$I1,\$A\$2:\$A\$15,\$J\$1:\$J1,\$B\$2:\$B\$15,\$K\$1:\$K1,\$C\$2:\$C\$15,\$L\$1:\$L1,\$D\$2:\$D\$15)=0,COUNTIFS(\$A\$2:\$A\$15,\$A\$2:\$A\$15,\$B\$2:\$B\$15,\$B\$2:\$B\$15,\$C\$2:\$C\$15,\$C\$2:\$C\$15,\$D\$2:\$D\$15,\$D\$2:\$D\$15)),1),ROW(\$A\$2:\$A\$15)-ROW(\$A\$2)+1)))

Fill it right and down through L5 (or farther, if you want more than the top 4). You can use the following in M2 (non-array) to count the number of appearances:

=COUNTIFS(\$A\$2:\$A\$15,\$I2,\$B\$2:\$B\$15,\$J2,\$C\$2:\$C\$15,\$K2,\$D\$2:\$D\$15,\$L2)

For the other group, just adjust the formulas for the new range, so array-enter the following in T2:

=INDEX(N\$2:N\$15,MIN(IF(IF(COUNTIFS(\$T\$1:\$T1,\$N\$2:\$N\$15,\$U\$1:\$U1,\$O\$2:\$O\$15,\$V\$1:\$V1,\$P\$2:\$P\$15)=0,COUNTIFS(\$N\$2:\$N\$15,\$N\$2:\$N\$15,\$O\$2:\$O\$15,\$O\$2:\$O\$15,\$P\$2:\$P\$15,\$P\$2:\$P\$15))=LARGE(IF(COUNTIFS(\$T\$1:\$T1,\$N\$2:\$N\$15,\$U\$1:\$U1,\$O\$2:\$O\$15,\$V\$1:\$V1,\$P\$2:\$P\$15)=0,COUNTIFS(\$N\$2:\$N\$15,\$N\$2:\$N\$15,\$O\$2:\$O\$15,\$O\$2:\$O\$15,\$P\$2:\$P\$15,\$P\$2:\$P\$15)),1),ROW(\$N\$2:\$N\$15)-ROW(\$N\$2)+1)))

and fill right through V and down. Then in W2 (non-array):

=COUNTIFS(\$N\$2:\$N\$15,\$T2,\$O\$2:\$O\$15,\$U2,\$P\$2:\$P\$15,\$V2)

I think this setup returns the values you're looking for. It's also worth noting that my formulas do not depend at all on columns E:H or Q:S, so you can delete those sections if you like. The formulas simply return the top occurring matches in each set of initial values. Take a look at the attachment to see if it works as desired:

3. Re: Finding and arranging rows then displaying their frequency in descending order:

Cheers CAntosh , that concept was magical

There are currently 1 users browsing this thread. (0 members and 1 guests)