+ Reply to Thread
Results 1 to 15 of 15

Multi level sort

  1. #1
    Registered User
    Join Date
    06-09-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    8

    Multi level sort

    Hello there, a two stage question on custom sorting.


    Attachment 465130

    Aims of the sort:

    Stage one: C should equal the number of unique values of code B from linked to location value of A, both ar text strings but must return a number as the total unique values present.
    ie: if three values of B are are associated with a value of AA01 in A this would = 3

    Stage two: E is the summed score of SR2016 values and should equal all values of D that are associated with a unique location value in A
    ie: if three "B" codes are present in location A3 this would = 3

    what would you lovely people recommend to start tackling this logically?
    Many thanks in advance!
    Mr ferret ~
    Attached Images Attached Images

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,018

    Re: Multi level sort

    Hi, welcome to the forum

    Looks like your attachment did not come through, and many members are unable to see pics, so can you try the upload again please?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-09-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    8

    Re: Multi level sort

    The data:

    HTML Code: 
    Does this make more sense?

    Aims of the sort:

    Stage one: “C” should equal the number of unique values of code “B” from linked to location value of “A”, both ar text strings but must return a number as the total unique values present.
    ie: if three values of “B” are are associated with a value of AA01 in “A” this would = 3

    Stage two: “E” is the summed score of SR2016 values and should equal all values of “D” that are associated with a unique location value in “A”
    ie: if three "B" codes are present in location “A3” this would = 3

    Mr ferret ~
    Last edited by Mr Ferret; 06-13-2016 at 09:57 AM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,307

    Re: Multi level sort

    If I understand correctly you want to count the number of unique codes in column B that are associated with each location in column A. For that part of the solution I used two helper columns (which could be hidden for aesthetic purposes) and the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I came up with a different value for location AA04 as there are two unique codes associated.
    For column E try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This does give some different values from those listed in post #3, however the values yielded by the formula seem to be correct, again assuming that my understanding of the OP is correct.
    Let me know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    06-09-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    8

    Re: Multi level sort

    Thats Excellent!
    thank-you JeteMc your a live saver. indeed i just realized when i read this that i had made a mistake in AA04 so you are quite cio
    I have edited it so its ranges of whole columns ie:

    C:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    E: (added scores)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    G: (occurrence)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    this makes it easier for me to add new data, i think it should still work fine as i have tested...
    Mr Ferret

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,307

    Re: Multi level sort

    You're Welcome, thank you for the feedback and marking the thread 'Solved'. I hope that you have a blessed day.

  7. #7
    Registered User
    Join Date
    06-09-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    8

    Re: Multi level sort

    OK not quite working yet i have realized...
    as the scores of duplicate combos are still being counted!
    Adding a row:[11]
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    E should = 20 (as it does currently ie the sum of unique values in D3+D8+D9)
    ...but infact E=28 (counting the duplicate of D11)
    Hope that makes sense
    Mr Ferret
    Last edited by Mr Ferret; 06-14-2016 at 10:39 AM.

  8. #8
    Registered User
    Join Date
    06-09-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    8

    Re: Multi level sort

    A separate issue in C: is that if you are only doing COUNTIF on G=1 then all those duplicates are not counted
    Rather than just one value of D of each unique combo of A&B being counted and not all subsequent duplicate valued of A&B which is what is needed.
    Cheers in advance for looking at these
    Mr Ferret

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,307

    Re: Multi level sort

    This is a response to post #7, not sure that I understand post #8. Perhaps if you could fill in the expected values for column C it will be clear what you want.
    Modify the formula for G2 and down to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Modify the formula for E2 and down to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let me know if you have any questions.
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,307

    Re: Multi level sort

    After further consideration it seems that the following formula in C2 and copied down will give you the values your want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let me know if you have any questions.

  11. #11
    Registered User
    Join Date
    06-09-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    8

    Re: Multi level sort

    Im not quite sure what this change does im afraid...
    and since the last post have corrected (abet in a little long winded fashion) to make Col C show the correct number using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    this works by adding three new cols:
    the first col filters for values other than 1 using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    the second filters the duplicate sets and labels the first duplicate in each set as 1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and the third combines these into a list of all unique rows to count the values in C:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    what i have not managed yet is to make Col E be filtered by this new list of unique values: See below.

    PS i have adder more duplicates so its clearer what the issue is
    Attached Files Attached Files
    Last edited by Mr Ferret; 06-14-2016 at 05:59 PM.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,307

    Re: Multi level sort

    Try pasting the following formula in E3 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let me know if you have any questions.

  13. #13
    Registered User
    Join Date
    06-09-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    8

    Re: Multi level sort

    That works a treat, thankyou kindly!
    Well thanks to you I now have a working solution, I wonder if can streamline all these "working" columns (G-K) into fewer with a single formula...
    Last edited by Mr Ferret; 06-14-2016 at 07:39 PM.

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,307

    Re: Multi level sort

    Quote Originally Posted by Mr Ferret View Post
    I now have a working solution, I wonder if can streamline all these "working" columns (G-K) into fewer with a single formula...
    My personal opinion is that it isn't worth the effort. Excel provides 16,384 columns so using 5 for helpers isn't a big deal. Secondly, what you have now is kind of like a car with a carburetor, easier to fix if it starts acting up/needs an adjustment. Go to a complicated single formula solution and its like the new cars with the solid state circuitry/computer chips, you'll probably need some help tuning it if *anything* happens. I hope that you have a blessed day.

  15. #15
    Registered User
    Join Date
    06-09-2016
    Location
    UK
    MS-Off Ver
    2010
    Posts
    8

    Re: Multi level sort

    Yes that is a fair point but as i am going to be creating many files using this and each are 100,000 rows at least i may go the other route and look into transferring it into VB.
    Thanks gain for all your help.
    Mr Ferret.

+ 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. [SOLVED] multi level sort in powerpivot
    By nsr1989 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-21-2013, 07:53 PM
  2. Bill of Materials conversion from multi level to single level
    By susmitpatel in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-11-2013, 12:53 AM
  3. Multi level BOM
    By neorez in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-23-2010, 05:22 PM
  4. Multi-Level reporting
    By sugrue in forum Excel General
    Replies: 9
    Last Post: 05-22-2008, 12:41 PM
  5. Multi-Level VLOOKUP
    By jibjabmraz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2007, 01:40 PM
  6. Using macro to convert single level BOM to Multi Level BOM
    By andrew_chong in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-07-2006, 04:57 PM
  7. Multi-level passwords
    By Todor in forum Excel General
    Replies: 7
    Last Post: 06-07-2005, 02:05 PM

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