How to Filter data with criteria and sort it accordingly to another List rank position?

[Lacan]

Hi Guys,

Would like all the help you can give.

Quite simple, in cell B5 would like to Filter and return data headers with criteria is to search both Bricks simultaneous 301 Lx - NEW YORK (Venteira) and 302 Lx - NEW YORK (Alfragide, ÁLivres) which sales has to be >0.
After that steep sort it accordingly with rank brick position in ascending order.
All of that in one unique formula.
Feel free if need more additional explanation.
Thank you very much!!

Fórmula Principal para o Ficheiro VDF.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2													BRICK	NAME	CODE	SALES
3							FILTERED CRITERIA			SORT CRITERIA			299 Lx - NEW YORK (MÁgua - Sul)	NEW YORK	D07	6999
4		BRICK	NAME	CODE	SALES		BRICK	SALES >0		BRICK RANK	ORDER		299 Lx - NEW YORK (MÁgua - Sul)	CENTRAL - NEW YORK	D05	1503
5							301 Lx - NEW YORK (Venteira)			299 Lx - NEW YORK (MÁgua - Sul)	6		299 Lx - NEW YORK (MÁgua - Sul)	DIAS & BRITO	D04	0
6							302 Lx - NEW YORK (Alfragide, ÁLivres)			300 Lx - NEW YORK (MÁgua - Norte)	2		299 Lx - NEW YORK (MÁgua - Sul)	CARLOS - NEW YORK	D03	0
7										301 Lx - NEW YORK (Venteira)	4		299 Lx - NEW YORK (MÁgua - Sul)	DE CARENQUE	D01	0
8										302 Lx - NEW YORK (Alfragide, ÁLivres)	3		300 Lx - NEW YORK (MÁgua - Norte)	ROMEIRO	D06	371
9										308 Lx - LONDON (Benfica - Sul)	5		300 Lx - NEW YORK (MÁgua - Norte)	NUNES	D03	3968
10										310 Lx - LONDON (Estrela)	1		300 Lx - NEW YORK (MÁgua - Norte)	SOARES CORREIA	D03	0
11													300 Lx - NEW YORK (MÁgua - Norte)	DO GUIZO	D02	0
12													300 Lx - NEW YORK (MÁgua - Norte)	TANARA NEW YORK	D01	12012
13													301 Lx - NEW YORK (Venteira)	CONTINENTE NEW YORK	D08	0
14													301 Lx - NEW YORK (Venteira)	CLABEL	D07	0
15													301 Lx - NEW YORK (Venteira)	HELENICA	D07	1641
16													301 Lx - NEW YORK (Venteira)	CAMPOS	D04	11955
17													308 Lx - LONDON (Benfica - Sul)	CAVACA	D04	0
18													308 Lx - LONDON (Benfica - Sul)	DO BOREL	D04	0
19													308 Lx - LONDON (Benfica - Sul)	IGREJA	D02	0
20													301 Lx - NEW YORK (Venteira)	JARDIM - NEW YORK	D02	0
21													301 Lx - NEW YORK (Venteira)	MELO - NEW YORK	D02	0
22													301 Lx - NEW YORK (Venteira)	SAO JORGE	D02	0
23													301 Lx - NEW YORK (Venteira)	CONFIANCA - NEW YORK	D01	0
24													302 Lx - NEW YORK (Alfragide, ÁLivres)	DAMAIA	D09	131
25													302 Lx - NEW YORK (Alfragide, ÁLivres)	DA QUINTA GRANDE	D07	1842
26													302 Lx - NEW YORK (Alfragide, ÁLivres)	HELENO	D07	530
27													302 Lx - NEW YORK (Alfragide, ÁLivres)	REMEDIOS	D07	0
28													310 Lx - LONDON (Estrela)	GASPAR POTE	D04	3216
29													310 Lx - LONDON (Estrela)	D JOAO V	D03	0
30													302 Lx - NEW YORK (Alfragide, ÁLivres)	VAZ MARTINS	D03	700
31													302 Lx - NEW YORK (Alfragide, ÁLivres)	CONFIANCA - DAMAIA	D02	0
32
33
34
20-07-2024

 

[hagia_sofia]

Something like this?

=LET(
filter,FILTER(M3:P31,((M3:M31=G5)+(M3:M31=G6))*(P3:P31>0)),
sort,XLOOKUP(CHOOSECOLS(filter,1),J5:J10,K5:K10),
SORTBY(filter,sort))

 

[alansidman]

Or an alternative with Power Query

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"BRICK"}, T2, {"BRICK"}, "T3", JoinKind.LeftOuter),
    ExpandedT3 = Table.ExpandTableColumn(MQ, "T3", {"SALES"}, {"SALES"}),
    FilteredRows = Table.SelectRows(ExpandedT3, each [SALES] > 0),
    CountRows = Table.Group(FilteredRows, {"BRICK"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Sort = Table.Sort(CountRows,{{"BRICK", Order.Ascending}})
in
    Sort

 

[Lacan]

Something like this?

=LET(
filter,FILTER(M3:P31,((M3:M31=G5)+(M3:M31=G6))*(P3:P31>0)),
sort,XLOOKUP(CHOOSECOLS(filter,1),J5:J10,K5:K10),
SORTBY(filter,sort))

Dear @hagia_sofia

Fits like a glove - Perfect!!!
If you don´t mind keeping the same Formula can you please update and add 2 new Sorts: First for the "Sales" (descending order) and then for "Code"(descending order)?
Thank you very much!!!

 

[Lacan]

Or an alternative with Power Query

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T1, {"BRICK"}, T2, {"BRICK"}, "T3", JoinKind.LeftOuter),
    ExpandedT3 = Table.ExpandTableColumn(MQ, "T3", {"SALES"}, {"SALES"}),
    FilteredRows = Table.SelectRows(ExpandedT3, each [SALES] > 0),
    CountRows = Table.Group(FilteredRows, {"BRICK"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Sort = Table.Sort(CountRows,{{"BRICK", Order.Ascending}})
in
    Sort

Dear @alansidman

Thank you very much for the help.

 

[hagia_sofia]

Dear @hagia_sofia

Fits like a glove - Perfect!!!
If you don´t mind keeping the same Formula can you please update and add 2 new Sorts: First for the "Sales" (descending order) and then for "Code"(descending order)?
Thank you very much!!!

Like this?

=LET(
filter,FILTER(M3:P31,((M3:M31=G5)+(M3:M31=G6))*(P3:P31>0)),
sort,XLOOKUP(CHOOSECOLS(filter,1),J5:J10,K5:K10),
SORTBY(filter,sort,1,CHOOSECOLS(filter,4),-1,CHOOSECOLS(filter,3),-1))

 

[Lacan]

Like this?

=LET(
filter,FILTER(M3:P31,((M3:M31=G5)+(M3:M31=G6))*(P3:P31>0)),
sort,XLOOKUP(CHOOSECOLS(filter,1),J5:J10,K5:K10),
SORTBY(filter,sort,1,CHOOSECOLS(filter,4),-1,CHOOSECOLS(filter,3),-1))

Dear @hagia_sofia

Brilliant!!!
Thank you very much!!!

 

[Lacan]

Dear @hagia_sofia

Brilliant!!!
Thank you very much!!!

Dear @hagia_sofia

Just one last single update, after filter data would like to return shorten text data only in column "BRICK".
To give an example, instead of they return like:

299 Lx - NEW YORK (MÁgua - Sul)
302 Lx - NEW YORK (Alfragide, ÁLivres)

Prefer much more like this:

299 (MÁgua - Sul)
302 (Alfragide, ÁLivres)

Can you please update it in your last Formula?

Think Im done now!!!
Thank you very much!!!!

 

[hagia_sofia]

Test this - it is even longer than before:

=LET(
brick,O3:O31,
filter,FILTER(O3:R31,((brick=G5)+(brick=G6))*(R3:R31>0)),
sort,XLOOKUP(CHOOSECOLS(filter,1),J5:J10,K5:K10),
array,SORTBY(filter,sort,1,CHOOSECOLS(filter,4),-1,CHOOSECOLS(filter,3),-1),
brick_alt,TEXTBEFORE(brick," ",1)&" "&MID(brick,FIND("(",brick),LEN(brick)-FIND("(",brick)+1),
replace,XLOOKUP(CHOOSECOLS(array,1),brick,brick_alt),
HSTACK(replace,CHOOSECOLS(array,2,3,4)))

 

[Lacan]

Dear @hagia_sofia

Perfect as always!!!
Thank you very much!!!