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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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

M
  • Solved
Formula Help.
Replies
3
Views
63
Fluff
Fluff
H
  • Solved
Search for topics based on their groups.
Replies
7
Views
141
Peter_SSs
Peter_SSs
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
317
Peter_SSs
Peter_SSs
M
  • Question
Best solution to consolidate values that occur on multiple dates/lines
Replies
5
Views
269
Peter_SSs
Peter_SSs
H
  • Question
using sorting and array to deal with big data for expected result
Replies
3
Views
107
Hasson
H

Forum statistics

Threads
1,215,056
Messages
6,122,907
Members
449,096
Latest member
dbomb1414

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