How to use Filter Function, Unique Function and Large Function all together?

L

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
163
Office Version
  1. 365
Platform
  1. Windows
Hello Guys, ??

Have these table below and using Filter Function, Unique Function and Large Function all together.

As you can check by yourself I think Im near finish the formula.

However can´t get the product as Unique one and the match units as the Largest one.

Can you help please? (y)??

Thank you very much

Livro1.xlsx
BCDEFGHIJKLMNOPQRSTUVW
2BrickNameCodeDateMonthProductFacturadasCategoríaUnitsBrutoNeto
3NAME299 Lx - AMATEORA (MÁgua - Sul)AMATEORA303608407/01/2020ene. 2020DICLOFENAC ALTER 50 MG - 60 COMP.FacturadasGD113,052,04
4AMATEORA299 Lx - AMATEORA (MÁgua - Sul)AMATEORA303608407/02/2020feb. 2020DICLOFENAC ALTER 50 MG - 60 COMP.FacturadasGD126,104,09
501/01/2021299 Lx - AMATEORA (MÁgua - Sul)AMATEORA303608410/03/2020mar. 2020DICLOFENAC ALTER 50 MG - 60 COMP.FacturadasGD126,104,09
6299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458907/01/2020ene. 2020ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1212,5211,89
7NameCodeProductUnits299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458907/02/2020feb. 2020ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1425,0423,79
8AMATEORA4017083AMLODIPINA ALTER 10 mg - 60 Com20299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458919/06/2020jun. 2020ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD119,095,95
9AMATEORA4017083AMLODIPINA ALTER 10 mg - 60 Com6299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458922/07/2020jul. 2020ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1218,1811,89
10AMATEORA4017083AMLODIPINA ALTER 10 mg - 60 Com15299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458907/09/2020sep. 2020ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1318,7817,84
11AMATEORA4017083AMLODIPINA ALTER 10 mg - 60 Com10299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458912/10/2020oct. 2020ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1425,0423,79
12AMATEORA4017083AMLODIPINA ALTER 10 mg - 60 Com3299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458911/11/2020nov. 2020ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1425,0423,79
13AMATEORA4017083AMLODIPINA ALTER 10 mg - 60 Com14299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458904/02/2021feb. 2021ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1212,528,39
14AMATEORA4017083AMLODIPINA ALTER 10 mg - 60 Com12299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458904/03/2021mar. 2021ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1318,7817,84
15AMATEORA3404589ESPIRONOLACTONA ALTER 100 MG - 60 COMP.2299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458929/04/2021abr. 2021ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1318,7817,84
16AMATEORA3404589ESPIRONOLACTONA ALTER 100 MG - 60 COMP.3299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458906/07/2021jul. 2021ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1212,529,64
17AMATEORA4640884NIFEDIPINA ALTER 20 MG - 60 COMP.1299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458907/09/2021sep. 2021ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1212,529,64
18AMATEORA4640884NIFEDIPINA ALTER 20 MG - 60 COMP.2299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458906/10/2021oct. 2021ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1212,529,64
19AMATEORA4140182PRAVASTATINA ALTER 20 MG - 60 COMP.1299 Lx - AMATEORA (MÁgua - Sul)AMATEORA340458907/12/2021dic. 2021ESPIRONOLACTONA ALTER 100 MG - 60 COMP.FacturadasGD1212,529,64
20AMATEORA4012381SINVASTATINA ALTER 20 MG - 60 COMP.50299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238107/01/2020ene. 2020SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD1100252,00168,84
21AMATEORA4012381SINVASTATINA ALTER 20 MG - 60 COMP.40299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238110/03/2020mar. 2020SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD155138,6092,86
22AMATEORA4012381SINVASTATINA ALTER 20 MG - 60 COMP.60299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238106/04/2020abr. 2020SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD1125477,50211,05
23AMATEORA4012381SINVASTATINA ALTER 20 MG - 60 COMP.45299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238119/06/2020jun. 2020SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD12595,5042,21
24AMATEORA4012381SINVASTATINA ALTER 20 MG - 60 COMP.65299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238122/07/2020jul. 2020SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD150191,0084,42
25299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238107/09/2020sep. 2020SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD1100252,00168,84
26299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238111/11/2020nov. 2020SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD1100267,00178,89
27299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238108/01/2021ene. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD150133,50102,80
28299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238104/02/2021feb. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD140106,8071,56
29299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238104/03/2021mar. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD150133,50102,80
30299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238106/04/2021abr. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD160139,20107,18
31299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238129/04/2021abr. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD145104,4080,39
32299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238104/06/2021jun. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD150116,0089,32
33299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238106/07/2021jul. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD150116,0089,32
34299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238104/08/2021ago. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD14092,8071,46
35299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238107/09/2021sep. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD150116,0089,32
36299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238106/10/2021oct. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD165150,80116,12
37299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238110/11/2021nov. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD14092,8071,46
38299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401238107/12/2021dic. 2021SINVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD150116,0089,32
39299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708307/01/2020ene. 2020AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD140120,4080,67
40299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708306/04/2020abr. 2020AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD130135,0062,71
41299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708319/06/2020jun. 2020AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD1522,5010,45
42299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708322/07/2020jul. 2020AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD1836,4016,94
43299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708307/09/2020sep. 2020AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD11650,5633,88
44299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708312/10/2020oct. 2020AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD11444,2429,64
45299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708311/11/2020nov. 2020AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD12991,6461,40
46299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708308/01/2021ene. 2021AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD12063,2048,66
47299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708304/02/2021feb. 2021AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD1618,9612,70
48299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708304/03/2021mar. 2021AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD11547,4036,50
49299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708329/04/2021abr. 2021AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD11031,6024,33
50299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708304/06/2021jun. 2021AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD139,487,30
51299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708306/07/2021jul. 2021AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD11444,2434,06
52299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708304/08/2021ago. 2021AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD11031,6024,33
53299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708307/09/2021sep. 2021AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD1618,9614,60
54299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708306/10/2021oct. 2021AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD11237,9229,20
55299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708310/11/2021nov. 2021AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD11444,2434,06
56299 Lx - AMATEORA (MÁgua - Sul)AMATEORA401708307/12/2021dic. 2021AMLODIPINA ALTER 10 mg - 60 ComFacturadasGD11237,9229,20
57299 Lx - AMATEORA (MÁgua - Sul)AMATEORA414018210/03/2020mar. 2020PRAVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD116,624,44
58299 Lx - AMATEORA (MÁgua - Sul)AMATEORA414018206/04/2020abr. 2020PRAVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD1219,008,87
59299 Lx - AMATEORA (MÁgua - Sul)AMATEORA414018207/09/2020sep. 2020PRAVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD116,624,44
60299 Lx - AMATEORA (MÁgua - Sul)AMATEORA414018212/10/2020oct. 2020PRAVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD116,624,44
61299 Lx - AMATEORA (MÁgua - Sul)AMATEORA414018204/06/2021jun. 2021PRAVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD116,615,09
62299 Lx - AMATEORA (MÁgua - Sul)AMATEORA414018204/08/2021ago. 2021PRAVASTATINA ALTER 20 MG - 60 COMP.FacturadasGD116,615,09
63299 Lx - AMATEORA (MÁgua - Sul)AMATEORA464088407/01/2020ene. 2020NIFEDIPINA ALTER 20 MG - 60 COMP.FacturadasGD114,003,80
64299 Lx - AMATEORA (MÁgua - Sul)AMATEORA464088407/02/2020feb. 2020NIFEDIPINA ALTER 20 MG - 60 COMP.FacturadasGD114,003,80
65299 Lx - AMATEORA (MÁgua - Sul)AMATEORA464088406/04/2021abr. 2021NIFEDIPINA ALTER 20 MG - 60 COMP.FacturadasGD114,003,80
66299 Lx - AMATEORA (MÁgua - Sul)AMATEORA464088404/06/2021jun. 2021NIFEDIPINA ALTER 20 MG - 60 COMP.FacturadasGD114,003,80
67299 Lx - AMATEORA (MÁgua - Sul)AMATEORA464088404/08/2021ago. 2021NIFEDIPINA ALTER 20 MG - 60 COMP.FacturadasGD128,007,60
68299 Lx - AMATEORA (MÁgua - Sul)AMATEORA464088407/09/2021sep. 2021NIFEDIPINA ALTER 20 MG - 60 COMP.FacturadasGD128,007,60
69299 Lx - AMATEORA (MÁgua - Sul)AMATEORA464088406/10/2021oct. 2021NIFEDIPINA ALTER 20 MG - 60 COMP.FacturadasGD114,003,80
70299 Lx - AMATEORA (MÁgua - Sul)AMATEORA464088410/11/2021nov. 2021NIFEDIPINA ALTER 20 MG - 60 COMP.FacturadasGD128,007,60
71299 Lx - AMATEORA (MÁgua - Sul)AMATEORA473678107/02/2020feb. 2020PRAVASTATINA ALTER 40 MG - 60 COMP.FacturadasGD119,166,14
72299 Lx - AMATEORA (MÁgua - Sul)AMATEORA473678110/03/2020mar. 2020PRAVASTATINA ALTER 40 MG - 60 COMP.FacturadasGD119,166,14
73299 Lx - AMATEORA (MÁgua - Sul)AMATEORA478058107/01/2020ene. 2020AMLODIPINA ALTER 5 mg - 60 ComFacturadasGD160107,4071,96
74299 Lx - AMATEORA (MÁgua - Sul)AMATEORA478058107/02/2020feb. 2020AMLODIPINA ALTER 5 mg - 60 ComFacturadasGD11526,8517,99
75299 Lx - AMATEORA (MÁgua - Sul)AMATEORA478058106/04/2020abr. 2020AMLODIPINA ALTER 5 mg - 60 ComFacturadasGD170215,6088,17
Folha1
Cell Formulas
RangeFormula
B8:E24B8=UNIQUE(SORT(FILTER(FILTER(N3:U53243,(P3:P53243>=B5)*(N3:N53243=B4)),{1,1,0,0,1,0,0,1}),3,1),,0)
Dynamic array formulas.
 
Sort by date Sort by votes
That's an easy update:

Excel Formula: 
=LET(
name,J3:J99,
code,K3:K99,
product,N3:N99,
date,L3:L99,
units,Q3:Q99,
f,UNIQUE(FILTER(CHOOSE({1,2,3},name,code,product),(name=B4))),
un,INDEX(f,0,1),
uc,INDEX(f,0,2),
up,INDEX(f,0,3),
maxunit,MAXIFS(units,name,un,code,uc,product,up),
maxdate,MAXIFS(date,name,un,code,uc,product,up),
maxdateunits,SUMIFS(units,name,un,code,uc,product,up,date,maxdate),
SORT(CHOOSE({1,2,3,4,5,6},un,uc,up,maxunit,maxdate,maxdateunits),3))
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Eric W said:
That's an easy update:

Excel Formula: 
=LET(
name,J3:J99,
code,K3:K99,
product,N3:N99,
date,L3:L99,
units,Q3:Q99,
f,UNIQUE(FILTER(CHOOSE({1,2,3},name,code,product),(name=B4))),
un,INDEX(f,0,1),
uc,INDEX(f,0,2),
up,INDEX(f,0,3),
maxunit,MAXIFS(units,name,un,code,uc,product,up),
maxdate,MAXIFS(date,name,un,code,uc,product,up),
maxdateunits,SUMIFS(units,name,un,code,uc,product,up,date,maxdate),
SORT(CHOOSE({1,2,3,4,5,6},un,uc,up,maxunit,maxdate,maxdateunits),3))
Click to expand...

Dear @Eric W

Works Perfect as always!!!! 🔝🔝👍
Thank you very!!! 🙏🙏👊
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
How to extract data using extensions Names and Complete Names ???
Replies
3
Views
354
Lacan
L
L
  • Question
How to filter function and SUM based on Partial Text Match (SUMIFS with wildcards)
2
Replies
17
Views
764
Lacan
L
L
  • Question
How to extract records that contains a specific text words? 🙏🙌
2
Replies
10
Views
767
Lacan
L
R
  • Solved
Unique values from multiple columns
Replies
4
Views
257
ravi2628
R
L
  • Question
Very simple and easy question: - How to use Indirect Function + Xlookup with 2 Criteria?
Replies
2
Views
319
Lacan
L

Forum statistics

Threads
1,215,528
Messages
6,125,338
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top