+ Reply to Thread
Results 1 to 15 of 15

Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Category)

  1. #1
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Category)

    I am having issues with the code shown below.

    Please Login or Register  to view this content.

    My problem is being able to use the original data (user input data) and transforming to show:

    • The three fruits with the highest quantities to display the name of the most frequent to least frequent defects. The defects may be unique to a particular fruit but the excel file is just an example. So the constraints would be: to formulate and rank the defects using the original data (no referencing by adding additional tables)
    • Display quantity of defects for each defect for the three fruits with highest quantities; the frequencies will always be listed from largest to smallest but the list order of defects may vary depending on their frequency (cells $R$1:$X$20)

    Other criteria is that more data will always be added to the original data. It is semi-dynamic. The limit has never gone over 500 rows.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    What is the exact final results you are seeking based on the sample data?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    The exactly final results I am seeking are S2:T10. I want to display the defects with the following criteria:
    • Fruit listed in S1
    • Defects listed from highest to lowest frequency in S2:S10.
    • The occurence of the frequency listed in T2:T10.
    • Original data is used to display the defects; no additional tables added for references.
    • N1:P10 can be used as reference for S1:T10, however.
    • Data may change or vary in D2:G100 but will never exceed past 250 lines (or rows)
    Last edited by T86157; 05-22-2012 at 04:40 PM.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    Maybe these?

    In T2:

    Please Login or Register  to view this content.
    copied down

    In S2:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER and copied down

    Then copy from S2 to T and bottom of your range, and paste to U2 and W2

  5. #5
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    Sorry, one more specification.

    • Sum similar defects and still list them from largest to smallest frequency

    I tested the functions you provided. If I were to add another defect for apricots under Qty Rejected, I get Premature listed twice under Column S with 12 and 2.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    I am out for the night... in the meantime, can you post a sample of this, and I can review it again tomorrow, if nobody else has tackled it by then....

  7. #7
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    The top box outline (R1:X7) in the excel file is the incorrect one.

    The bottom box outline (R9:X14) are the desired results. Everything else looks good like the ranking order of the defect and frequency. Just if there are duplicate defects, I'd like them to be summed up and then ranked.
    Attached Images Attached Images
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    Try this,

    S2, with CTRL+SHIFT+ENTER

    Please Login or Register  to view this content.
    T2, with just ENTER,

    Please Login or Register  to view this content.
    Then select S2:T2, copy across & down.
    Last edited by Haseeb Avarakkan; 05-22-2012 at 08:09 PM.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  9. #9
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    Problem is not solved
    Last edited by T86157; 05-23-2012 at 10:33 AM.

  10. #10
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    NBVC and Haseeb, I was using your formula, Haseeb and am still having probems with it. Sorry, I had to "unsolve" my thread.

    Please Login or Register  to view this content.

    Whenever I enter in two different defects for the same fruit with the same frequency of defects, it repeats a defect that the fruit several times depending on how many different defects have the same frequency. I posted a picture so you can see what is happening (problem is occuring in cell S4 and will continue repeating itself if I add another different defect for Apricot with a frequency of 8)
    Attached Images Attached Images

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    I am not going to try to disect Haseeb's formula.. he can revise as necessary, if he desires....

    In order to use my previous method and adapt your new requirements.. my suggestion would be to add a "helper column" in your database, so starting at J2:

    =IF(COUNTIFS(F$2:F2,F2,G$2:G2,G2)=1,SUMIFS(E$2:E$100,F$2:F$100,F2,G$2:G$100,G2),"") copied down.

    Then T2 would become:

    =IFERROR(LARGE(IF($F$2:$F$100=S$1,$J$2:$J$15),ROWS(T$2:T2)),"")

    confirmed with CSE keys

    In S2:

    =IF(T2="","",INDEX($G$2:$G$100,SMALL(IF($F$2:$F$100=S$1,IF($J$2:$J$100=T2,ROW($E$2:$E$100)-ROW($E$2)+1)),COUNTIF(T$2:T2,T2))))

    confirmed with CSE keys

    select S2:T2 and copy across and down.

  12. #12
    Forum Contributor
    Join Date
    05-17-2012
    Location
    CA, USA
    MS-Off Ver
    Microsoft Excel for Microsoft 365
    Posts
    163

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    NBVC, is there a way the "helper" column formula could be nested in with T2 and S2? Your suggestion solves the issue of repeated defects but I'm trying to minimize "clutter" by having the original data (user input, D2:I14) referenced directly to Columns N, O, P, S and T.

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    You can always copy the formula down 100 rows, and then hide the column.. or change it to a table, and hide the column, the formula will update as you enter data...

    Adding it to the T2 and S2 formulas only add to the complexity of the Array formulas, and then ultimately to the efficiency of the whole thing...

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    You can try this if you want to see if it is still efficient...

    If you can create a list of all the possible defects somewhere and name that range, Defects.

    Then in T2:

    =LARGE(SUMIFS($E$2:$E$20,$F$2:$F$20,S$1,$G$2:$G$20,Defects),ROWS(T$2:T2))

    Note: You can use an absolute range reference if you don't want to use Named Range

    CSE confirmed

    and in S2

    =IF(N(T2)=0,"",INDEX($G$2:$G$100,SMALL(IF($F$2:$F$100=S$1,IF($J$2:$J$100=T2,ROW($E$2:$E$100)-ROW($E$2)+1)),COUNTIF(T$2:T2,T2))))

    CSE confirmed

    Then copy S2:T2 over and across.

    To hide the 0's, format the cells as Custom, with Type: 0;-0;;@
    Last edited by NBVC; 05-23-2012 at 11:42 AM.

  15. #15
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Ranking & Displaying Unique Data: Multiple Criteria (Large to Small Frequency, Categor

    T86157,

    As NBVC shows using helper column would be better & easier to debug.

    Anyway in post#8, COUNTIF range was focusing only on S$1. :S1 was missing. try this.

    Please Login or Register  to view this content.
    Red highlighted was missing.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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