SORT(UNIQUE issue

[ollyhughes1982]

Hi,

Re screenshot below:

I have the below formula to give results in column B:

=IFERROR(SORT(UNIQUE(FILTER('All Completed VS'!C4:C2003,'All Completed VS'!E4:E2003<>"")),,-1),"")

In column C I then have:

=IF(B4="","",MAXIFS('All Completed VS'!$AZ$4:$AZ$2003,'All Completed VS'!$C$4:$C$2003,B4))

...which gives me the count for the values in column B.

Is there a way that I can change the sort so that the list shows in descending order of the values in column C? (i.e. Newport [10] at the top, then Riverfront [8], Penallta [4] and so on...) At the moment I can only get it to sort by column B. This just gives an alphabetical sort of column B.

Thanks in advance!

 

[ollyhughes1982]

Cool, glad I could help and thanks for the mark.

For the figure in the large yeallow box, do you know how it would be possible to automate that, please? I am struggling to do that. Now that the data is there it should be possible

 

[GraH]

What's inside the yellow box? What are you counting and how? Maybe a COUNTIFS does the job?

 

[ollyhughes1982]

What's inside the yellow box? What are you counting and how? Maybe a COUNTIFS does the job?

The logic is in the second cell on the page (A2) and I have put a manual calculation in the yellow box and explained it in the text next to it. I just want to be able to do this in a formula. Thanks again.

 

[ollyhughes1982]

It's basically the first column C value that is smaller than the value in column A (where they cross-over). If that makes sense.

 

[ollyhughes1982]

Hi again. Did you have a chance to have a look at the yellow box figure? Thanks again

 

[GraH]

Hi, sorry, no I'm without excel for the moment. I don't really get it from the picture dispite your effort to explain it.

 

[ollyhughes1982]

No problem. Thanks