# Multi level sort

1. ## 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?
Mr ferret ~

2. ## 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?

3. ## 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 ~

4. ## 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:
`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:
`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.

5. ## 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:
`Please Login or Register  to view this content.`

Formula:
`Please Login or Register  to view this content.`

G: (occurrence)
Formula:
`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. ## 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. ## Re: Multi level sort

OK not quite working yet i have realized...
as the scores of duplicate combos are still being counted!
Formula:
`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

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. ## 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:
`Please Login or Register  to view this content.`
Modify the formula for E2 and down to:
Formula:
`Please Login or Register  to view this content.`
Let me know if you have any questions.

10. ## 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:
`Please Login or Register  to view this content.`
Let me know if you have any questions.

11. ## 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:
`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:
`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:
`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:
`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

12. ## Re: Multi level sort

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

13. ## 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...

14. ## Re: Multi level sort

Originally Posted by Mr Ferret
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. ## 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.

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

#### 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