Dynamic Arrays - Top 5 values

L

Leticia

New Member
Joined
Jun 9, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Hi! I am having some difficulties with my dynamic arrays

I need to create two different dynamic arrays, in which I extract the Top 5 values aggregated by "Case".

In the first scenario, I would need to obtain the Top 5 values when SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4)+SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4)
In the second scenario, I would need to obtain the Top 5 values when SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSSING";TEST!$A:$A;"<="&$F$4)+SUMIFS(TEST!$F:$F;TEST!$E:$E;"<>R.";TEST!$E:$E;"<>7250";TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$D:$D;"<"&EDATE($F$4;1))

However, when creating my dynamic arrays, I am not capable of creating one for each scenario. Instead I created 3 different dynamic arrays which are not useful as omit part of the equation.
Excel Formula: 
=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$C:$C="MISSING")*(TEST!$A:$A<=$F$4))));SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!C:C="MISSING")*(TEST!$A:$A<=$F$4))))));SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$C:$C="MISSING")*(TEST!$A:$A<=$F$4)))));-1);SEQUENCE(5;1;1);{1\2});"-")

Excel Formula: 
=IFERROR(INDEX(SORT(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4))))));2;-1);SEQUENCE(5;1;1);{1\2});"-")

Excel Formula: 
=IFERROR(INDEX(SORT(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)*(TEST!$D:$D<EDATE($F$4;1))*(TEST!$E:$E<>"R.")*(TEST!$E:$E<>"7250"))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$D:$D;"<"&EDATE($F$4;1);TEST!$E:$E;"<>R.";TEST!$E:$E;"<>7250";TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)*(TEST!$D:$D<EDATE($F$4;1))*(TEST!$E:$E<>"R.")*(TEST!$E:$E<>"7250"))))));2;-1);SEQUENCE(5;1;1);{1\2});"-")

My results obtained are as following:
1625506587554.png


What I would want to obtain is (I did the table by summing the values manually - I would need it automatic)
1625506781378.png


I copy the test table in case it is useful
DATE (A)CASE (B)CHECK (C)SOLUTION (D)TYPE (E)VALUE (F)
21/05/2021PDONE28/05/20214,85
21/05/2021PDONE02/06/2021R.8.893,96
22/05/2021CAMISSING400.000,00
22/05/2021PDONE28/05/20211,67
22/05/2021CSDONE02/06/2021R.355.791,00
22/05/2021CSDONE02/06/2021R.358,64
22/05/2021CSDONE02/06/2021R.358,64
22/05/2021CSDONE02/06/2021R.355.791,00
22/05/2021CADONE15/06/2021400.000,00
25/05/2021CODONE28/05/2021100.000,00
25/05/2021PDONE01/06/20215,25
25/05/2021FDONE02/06/2021R.7.300.000,00
26/05/2021CADONE16/06/20211.718.000,00
26/05/2021CODONE01/06/2021422.832,98
26/05/2021CODONE02/06/2021R.1.086,77
26/05/2021CODONE02/06/2021R.1.086,77
26/05/2021CODONE02/06/2021R.983,74
26/05/2021CODONE03/06/202172509.244.707,41
26/05/2021CODONE03/06/202172508.336.807,00
26/05/2021CADONE15/06/20211.500.000,00
27/05/2021CAMISSING480.000,00
27/05/2021PDONE01/06/202115,75
27/05/2021CADONE02/06/2021R.4.363,23
27/05/2021CADONE02/06/2021R.4.363,23
27/05/2021CADONE15/06/2021400.000,00
28/05/2021PFDONE01/06/20210,97
28/05/2021FVDONE02/06/2021R.714.000,00
28/05/2021CADONE15/06/20211.428.000,00

Thank you very much.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Where is the explanation in words that Fluff asked for?

You have shown us the expected results but not given us any indication as to how you arrived at those results.
 
Upvote 0
jasonb75 said:
Where is the explanation in words that Fluff asked for?

You have shown us the expected results but not given us any indication as to how you arrived at those results.
Click to expand...
Yes, manually - I just summed the values un dynamic array 1+ those in dynamic array 2, and the same with 1 and 3.
However, as in this case there is only one value in 1 (CA - 880,000) only those cases where modified (the order didnt change).

Anyways, the manuality does not serve my purpose.
 
Upvote 0
For the 1st scenario are you trying to sum col F if A is less than F4 & D is either greater than F4 or blank?
 
Upvote 0
Then why didn't you say so? ;)
How about
+Fluff 1.xlsm
ABCDEFGHIJ
1DATE (A)CASE (B)CHECK (C)SOLUTION (D)TYPE (E)VALUE (F)
221/05/2021PDONE28/05/20214.8531/05/2021CO18,007,505
321/05/2021PDONE02/06/2021R.8893.96F7,300,000
422/05/2021CAMISSING400000CA6,334,726
522/05/2021PDONE28/05/20211.67FV714,000
622/05/2021CSDONE02/06/2021R.355791CS712,299
722/05/2021CSDONE02/06/2021R.358.64
822/05/2021CSDONE02/06/2021R.358.64
922/05/2021CSDONE02/06/2021R.355791
1022/05/2021CADONE15/06/2021400000
1125/05/2021CODONE28/05/2021100000
1225/05/2021PDONE01/06/20215.25
1325/05/2021FDONE02/06/2021R.7300000
1426/05/2021CADONE16/06/20211718000
1526/05/2021CODONE01/06/2021422832.98
1626/05/2021CODONE02/06/2021R.1086.77
1726/05/2021CODONE02/06/2021R.1086.77
1826/05/2021CODONE02/06/2021R.983.74
1926/05/2021CODONE03/06/202172509244707.41
2026/05/2021CODONE03/06/202172508336807
2126/05/2021CADONE15/06/20211500000
2227/05/2021CAMISSING480000
2327/05/2021PDONE01/06/202115.75
2427/05/2021CADONE02/06/2021R.4363.23
2527/05/2021CADONE02/06/2021R.4363.23
2627/05/2021CADONE15/06/2021400000
2728/05/2021PFDONE01/06/20210.97
2828/05/2021FVDONE02/06/2021R.714000
2928/05/2021CADONE15/06/20211428000
30
List
Cell Formulas
RangeFormula
I2:J6I2=INDEX(SORT(CHOOSE({1,2},UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))),SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,">="&H2,B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))))+SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,"",B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))))),2,-1),SEQUENCE(5),{1,2})
Dynamic array formulas.
 
Upvote 0
Solution
Fluff said:
Then why didn't you say so? ;)
How about
+Fluff 1.xlsm
ABCDEFGHIJ
1DATE (A)CASE (B)CHECK (C)SOLUTION (D)TYPE (E)VALUE (F)
221/05/2021PDONE28/05/20214.8531/05/2021CO18,007,505
321/05/2021PDONE02/06/2021R.8893.96F7,300,000
422/05/2021CAMISSING400000CA6,334,726
522/05/2021PDONE28/05/20211.67FV714,000
622/05/2021CSDONE02/06/2021R.355791CS712,299
722/05/2021CSDONE02/06/2021R.358.64
822/05/2021CSDONE02/06/2021R.358.64
922/05/2021CSDONE02/06/2021R.355791
1022/05/2021CADONE15/06/2021400000
1125/05/2021CODONE28/05/2021100000
1225/05/2021PDONE01/06/20215.25
1325/05/2021FDONE02/06/2021R.7300000
1426/05/2021CADONE16/06/20211718000
1526/05/2021CODONE01/06/2021422832.98
1626/05/2021CODONE02/06/2021R.1086.77
1726/05/2021CODONE02/06/2021R.1086.77
1826/05/2021CODONE02/06/2021R.983.74
1926/05/2021CODONE03/06/202172509244707.41
2026/05/2021CODONE03/06/202172508336807
2126/05/2021CADONE15/06/20211500000
2227/05/2021CAMISSING480000
2327/05/2021PDONE01/06/202115.75
2427/05/2021CADONE02/06/2021R.4363.23
2527/05/2021CADONE02/06/2021R.4363.23
2627/05/2021CADONE15/06/2021400000
2728/05/2021PFDONE01/06/20210.97
2828/05/2021FVDONE02/06/2021R.714000
2928/05/2021CADONE15/06/20211428000
30
List
Cell Formulas
RangeFormula
I2:J6I2=INDEX(SORT(CHOOSE({1,2},UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))),SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,">="&H2,B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))))+SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,"",B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))))),2,-1),SEQUENCE(5),{1,2})
Dynamic array formulas.
Click to expand...
Awesome! It worked! (Sorry for the delay I couldnt look at it until now)

I've been trying to obtain it for the second scenario, and I am having some issues too ?.
Excel Formula: 
=INDEX(SORT(CHOOSE({1\2};UNIQUE(FILTER(B2:B100;(A2:A100<=H2)*(E2:E100<>"R.")*(E2:E100<>"7250")*(((D2:D100>=H2)*(E2:E100<EDATE($H$2;1)))+(D2:D100=""))));SUMIFS(F2:F100;A2:A100;"<="&H2;D2:D100;">="&H2;E2:E100;"<>R.";E2:E100;"<>7250";B2:B100;UNIQUE(FILTER(B2:B100;(A2:A100<=H2)*(E2:E100<>"R.")*(E2:E100<>"7250")*(((D2:D100>=H2)*(D2:D100<EDATE($H$2;1)))+(D2:D100="")))))+SUMIFS(F2:F100;A2:A100;"<="&H2;D2:D100;"";E2:E100;"<>R.";E2:E100;"<>7250";B2:B100;UNIQUE(FILTER(B2:B100;(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100=""))))));2;-1);SEQUENCE(5);{1\2})

These are the results I am obtaining,
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
#N/A​
CA
5.446.000,00​
P
880.021,00​
when they should be
6.326 CA
21 P
0,97 PF
422.832 CO

What have I done wrong?

Thank you SO MUCH :)!
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFGHIJKLM
1DATE (A)CASE (B)CHECK (C)SOLUTION (D)TYPE (E)VALUE (F)
221/05/2021PDONE28/05/20214.8531/05/2021CO18,007,505CA6326000
321/05/2021PDONE02/06/2021R.8893.96F7,300,000CO422833
422/05/2021CAMISSING400000CA6,334,726P21
522/05/2021PDONE28/05/20211.67FV714,000PF0.97
622/05/2021CSDONE02/06/2021R.355791CS712,29900
722/05/2021CSDONE02/06/2021R.358.64
822/05/2021CSDONE02/06/2021R.358.64
922/05/2021CSDONE02/06/2021R.355791
1022/05/2021CADONE15/06/2021400000
1125/05/2021CODONE28/05/2021100000
1225/05/2021PDONE01/06/20215.25
1325/05/2021FDONE02/06/2021R.7300000
1426/05/2021CADONE16/06/20211718000
1526/05/2021CODONE01/06/2021422832.98
1626/05/2021CODONE02/06/2021R.1086.77
1726/05/2021CODONE02/06/2021R.1086.77
1826/05/2021CODONE02/06/2021R.983.74
1926/05/2021CODONE03/06/202172509244707.41
2026/05/2021CODONE03/06/202172508336807
2126/05/2021CADONE15/06/20211500000
2227/05/2021CAMISSING480000
2327/05/2021PDONE01/06/202115.75
2427/05/2021CADONE02/06/2021R.4363.23
2527/05/2021CADONE02/06/2021R.4363.23
2627/05/2021CADONE15/06/2021400000
2728/05/2021PFDONE01/06/20210.97
2828/05/2021FVDONE02/06/2021R.714000
2928/05/2021CADONE15/06/20211428000
30
List
Cell Formulas
RangeFormula
I2:J6I2=INDEX(SORT(CHOOSE({1,2},UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))),SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,">="&H2,B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))))+SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,"",B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))))),2,-1),SEQUENCE(5),{1,2})
L2:M6L2=INDEX(SORT(CHOOSE({1,2},UNIQUE(FILTER(B2:B100,(A2:A100<=H2)*(E2:E100<>"R.")*(E2:E100<>"7250")*((D2:D100>=H2)+(D2:D100="")))),SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,">="&H2,E2:E100,"<>R.",E2:E100,"<>7250",B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<=H2)*(E2:E100<>"R.")*(E2:E100<>"7250")*((D2:D100>=H2)+(D2:D100="")))))+SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,"",E2:E100,"<>R.",E2:E100,"<>7250",B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<=H2)*(E2:E100<>"R.")*(E2:E100<>"7250")*((D2:D100>=H2)+(D2:D100="")))))),2,-1),SEQUENCE(5),{1,2})
Dynamic array formulas.
 
Upvote 0
Fluff said:
How about
+Fluff 1.xlsm
ABCDEFGHIJKLM
1DATE (A)CASE (B)CHECK (C)SOLUTION (D)TYPE (E)VALUE (F)
221/05/2021PDONE28/05/20214.8531/05/2021CO18,007,505CA6326000
321/05/2021PDONE02/06/2021R.8893.96F7,300,000CO422833
422/05/2021CAMISSING400000CA6,334,726P21
522/05/2021PDONE28/05/20211.67FV714,000PF0.97
622/05/2021CSDONE02/06/2021R.355791CS712,29900
722/05/2021CSDONE02/06/2021R.358.64
822/05/2021CSDONE02/06/2021R.358.64
922/05/2021CSDONE02/06/2021R.355791
1022/05/2021CADONE15/06/2021400000
1125/05/2021CODONE28/05/2021100000
1225/05/2021PDONE01/06/20215.25
1325/05/2021FDONE02/06/2021R.7300000
1426/05/2021CADONE16/06/20211718000
1526/05/2021CODONE01/06/2021422832.98
1626/05/2021CODONE02/06/2021R.1086.77
1726/05/2021CODONE02/06/2021R.1086.77
1826/05/2021CODONE02/06/2021R.983.74
1926/05/2021CODONE03/06/202172509244707.41
2026/05/2021CODONE03/06/202172508336807
2126/05/2021CADONE15/06/20211500000
2227/05/2021CAMISSING480000
2327/05/2021PDONE01/06/202115.75
2427/05/2021CADONE02/06/2021R.4363.23
2527/05/2021CADONE02/06/2021R.4363.23
2627/05/2021CADONE15/06/2021400000
2728/05/2021PFDONE01/06/20210.97
2828/05/2021FVDONE02/06/2021R.714000
2928/05/2021CADONE15/06/20211428000
30
List
Cell Formulas
RangeFormula
I2:J6I2=INDEX(SORT(CHOOSE({1,2},UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))),SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,">="&H2,B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))))+SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,"",B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<>"")*(A2:A100<=H2)*((D2:D100>=H2)+(D2:D100="")))))),2,-1),SEQUENCE(5),{1,2})
L2:M6L2=INDEX(SORT(CHOOSE({1,2},UNIQUE(FILTER(B2:B100,(A2:A100<=H2)*(E2:E100<>"R.")*(E2:E100<>"7250")*((D2:D100>=H2)+(D2:D100="")))),SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,">="&H2,E2:E100,"<>R.",E2:E100,"<>7250",B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<=H2)*(E2:E100<>"R.")*(E2:E100<>"7250")*((D2:D100>=H2)+(D2:D100="")))))+SUMIFS(F2:F100,A2:A100,"<="&H2,D2:D100,"",E2:E100,"<>R.",E2:E100,"<>7250",B2:B100,UNIQUE(FILTER(B2:B100,(A2:A100<=H2)*(E2:E100<>"R.")*(E2:E100<>"7250")*((D2:D100>=H2)+(D2:D100="")))))),2,-1),SEQUENCE(5),{1,2})
Dynamic array formulas.
Click to expand...
Great! One last question ?

Can I put a "-" in both columns when the value is 0?
Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
M
  • Solved
Formula Help.
Replies
3
Views
444
Fluff
Fluff
youbitto
  • Solved
Copy paste from multiple Excel and Csv files to another excel file VBA
Replies
2
Views
207
youbitto
youbitto
H
  • Question Question
copy some columns based on the value in column "A".
Replies
9
Views
365
Cubist
Cubist
H
  • Solved
Retrieve the intersection of multiple cells with respect to columns and rows
2
Replies
10
Views
165
mumps
mumps

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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