+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Forum Contributor
    Join Date
    12-02-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    112

    Exclamation 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.

    Thank you so much for your admirable support.
    Attached Files Attached Files
    Last edited by LAVA2; 08-17-2018 at 08:39 AM.

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    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:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Forum Contributor
    Join Date
    12-02-2013
    Location
    england
    MS-Off Ver
    Excel 2007
    Posts
    112

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

    Cheers CAntosh , that concept was magical

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Arranging in Descending Order & Faster Solving
    By iqbalsingh9 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-15-2017, 04:11 AM
  2. [SOLVED] Arranging values from Series of Arrays in Descending Order by Formula - Not Macro
    By STUARTXL in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 06-19-2016, 06:18 AM
  3. [SOLVED] Organizing rows by zip codes in descending order
    By Najee in forum Excel General
    Replies: 3
    Last Post: 06-14-2016, 09:05 AM
  4. [SOLVED] Re-arranging rows based on pre-defined order
    By VBAnoobling in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2013, 03:38 PM
  5. ReOrder Rows in ascending/descending order based on date
    By kurb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2012, 05:42 PM
  6. Macro for arranging in descending order.
    By Taureankv in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-03-2010, 10:24 PM
  7. displaying data in descending order (automatically)
    By mrmiddleman in forum Excel General
    Replies: 5
    Last Post: 10-30-2007, 05:47 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1