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

Lacan

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
228
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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

Lacan
  • Solved
How to Filter data with criteria and sort it accordingly to another List rank position?
Replies
9
Views
275
Lacan
Lacan
Lacan
  • Solved
For 2 Columns how to Wraprows and then sort it horizontally accordingly with the rank List?
Replies
7
Views
265
Lacan
Lacan
Lacan
  • Solved
Filter data and return it with text short arrangements
Replies
5
Views
205
myall_blues
myall_blues
Lacan
  • Question Question
How to extract data using extensions Names and Complete Names ???
Replies
3
Views
451
Lacan
Lacan
Lacan
  • Question Question
How to filter function and SUM based on Partial Text Match (SUMIFS with wildcards)
2
Replies
17
Views
1K
Lacan
Lacan

Forum statistics

Threads
1,223,099
Messages
6,170,114
Members
452,302
Latest member
TaMere

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