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!

 

[GraH]

Hi,

since you are on 365, you can first build your table included the events and the counts, then use unique and finally sort on 2 indexes.

Technique shown below. You would need to adapt the formulas inside LET to your needs. I do believe it should be doable in a single let formula. It's still very new to me.

Book1
	A	B	C	D	E	F	G	H	I
1								river	4
2	1	new	10					s	1
3	2	river	8					pen	4
4	3	pen	4					wint
5	4	ponty	3					ponty	1
6	5	pontp	2					river	6
7	6	seven	2					river
8	7	p	1					t	1
9	8	r	1					river	8
10	9	s	1					river	1
11	10	t	1					wint	1
12	11	wint	1					pen	3
13	12	wool	1					river	3
14								seven	2
15								new	7
16								r	1
17								new	3
18								river	5
19								ponty	2
20								new	4
21								new	1
22								r
23								t
24								ponty
25								pontp	2
26								p
27								new	6
28								river	7
29								pen	2
30								new	5
31								pen	1
32								wool	1
33								new	9
34								wool
35								pontp	1
36								new	10
37								new	8
38								ponty	3
39								s
40								new
41								river	2
42								p	1
43								seven	1
44								new	2
45								seven
Sheet1

Cell Formulas
Range	Formula
A2:A13	A2	=SEQUENCE(ROWS(B2#))
B2:C13	B2	=SORT(UNIQUE(LET(Events,FILTER(H1:H45,I1:I45<>""), Cnts,MAXIFS(I1:I45,H1:H45,Events), Col,SEQUENCE(1,2), IFS(Col=1,Events,Col=2,Cnts))),{2;1},{-1;1})
Dynamic array formulas.

 

[ollyhughes1982]

Hi,

since you are on 365, you can first build your table included the events and the counts, then use unique and finally sort on 2 indexes.

Technique shown below. You would need to adapt the formulas inside LET to your needs.

Book1
	A	B	C	D	E	F	G	H	I
1								river	4
2	1	new	10					s	1
3	2	river	8					pen	4
4	3	pen	4					wint
5	4	ponty	3					ponty	1
6	5	pontp	2					river	6
7	6	seven	2					river
8	7	p	1					t	1
9	8	r	1					river	8
10	9	s	1					river	1
11	10	t	1					wint	1
12	11	wint	1					pen	3
13	12	wool	1					river	3
14								seven	2
15								new	7
16								r	1
17								new	3
18								river	5
19								ponty	2
20								new	4
21								new	1
22								r
23								t
24								ponty
25								pontp	2
26								p
27								new	6
28								river	7
29								pen	2
30								new	5
31								pen	1
32								wool	1
33								new	9
34								wool
35								pontp	1
36								new	10
37								new	8
38								ponty	3
39								s
40								new
41								river	2
42								p	1
43								seven	1
44								new	2
45								seven
Sheet1

Cell Formulas
Range	Formula
A2:A13	A2	=SEQUENCE(ROWS(B2#))
B2:C13	B2	=SORT(UNIQUE(LET(Events,FILTER(H1:H45,I1:I45<>""), Cnts,MAXIFS(I1:I45,H1:H45,Events), Col,SEQUENCE(1,2), IFS(Col=1,Events,Col=2,Cnts))),{2;1},{-1;1})
Dynamic array formulas.

Hi. Thanks for this, but I cannot resort the table. It is a table that is added to all the time and is always in date order that events / roles are completed.

 

[GraH]

Hi. Thanks for this, but I cannot resort the table. It is a table that is added to all the time and is always in date order that events / roles are completed.

Now I'm confused as your query is to sort the result based on the count. That's exactly what my formula does.

 

[ollyhughes1982]

Now I'm confused as your query is to sort the result based on the count. That's exactly what my formula does.

Apologies, I was thinking that the original source worksheet had to be rearranged? I cannot rearrange the source data, it stays in date completed order. I will try your formula now. Could you possibly try your solution within my file, so that the references will correct for that table, please?

 

[GraH]

Apologies, I was thinking that the original source worksheet had to be rearranged?

No, I simply used your formulas inside the let function.

Could you possibly try your solution within my file, so that the references will correct for that table, please?

try

SORT(
UNIQUE(
LET(
Events;FILTER('All Completed VS'!C4:C2003,'All Completed VS'!E4:E2003<>"");
Cnts;MAXIFS('All Completed VS'!$AZ$4:$AZ$2003,'All Completed VS'!$C$4:$C$2003,Events);
Col;SEQUENCE(1;2);
IFS(Col=1;Events;Col=2;Cnts)
)
)
;{2;1};{-1;1})

 

[ollyhughes1982]

Apologies, I was thinking that the original source worksheet had to be rearranged? I cannot rearrange the source data, it stays in date completed order. I will try your formula

No, I simply used your formulas inside the let function.

try

SORT(
UNIQUE(
LET(
Events;FILTER('All Completed VS'!C4:C2003,'All Completed VS'!E4:E2003<>"");
Cnts;MAXIFS('All Completed VS'!$AZ$4:$AZ$2003,'All Completed VS'!$C$4:$C$2003,Events);
Col;SEQUENCE(1;2);
IFS(Col=1;Events;Col=2;Cnts)
)
)
;{2;1};{-1;1})

Entering this into cell B4 gives me an error. Screenshot below:

If I include the CODE parts, the formula just doesn't do anything.

 

[GraH]

I am on EU settings, so I use ";" where you are using using ",".

 

[ollyhughes1982]

Great, that's working now

 

[GraH]

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