Assistance with FILTER Function and Dynamic Range in Split Arrays (Excel 2021)

I

irfman

New Member
Joined
Jan 1, 2019
Messages
19
Office Version
  1. 2021
Hi,

I am encountering an issue with the FILTER function when working with a split array.

Table 1: Contains a list of sample customers with quarter-end balances.

Table 2: Contains unique combo IDs based on the Name and eCIB columns extracted from Table 1, with values summed based on Combo 3.

Table 3: Displays a sorted list from Table 2, filtering changes greater than or less than 10.

The issue arises because Name and eCIB are derived from a split array using the UNIQUE formula. Due to this, the SORT function combined with the FILTER formula in Table 3 cannot reference a dynamic range using #. As a result, I have to specify a fixed range manually.

Is there a way to make the range dynamic?

Any help or suggestions would be greatly appreciated. Thanks a ton!

Regards
Irfan

Sample.xlsx
Q
15
Sheet3

1743001367148.png

1743001290170.png
 
Sort by date Sort by votes
I am not sure if my query is easily understandable, so for clarity, I am also posting the tables below in case the data needs to be copied into an Excel file to find a resolution.

Thank you very much, and I apologize if this violates any forum rules. I hope for your understanding and patience.

Thanks!


Table 1: List of Sample Customers with Quarter-End Balances
NameeCIB Q1-24 Q2-24
A Majeed And Sons26189 0
A Majeed And Sons26100 0
A F Ferguson And Co6028 -135
Abdul Sattar Noor Muhammad And Co266141 40
Acme Mills Pvt Ltd335148 6
Adamjee Engineering Pvt Ltd35533 -1
Agriauto Ind Ltd442199 0
Karachi Institute Of Economics And Technology10051786 -0
Paf Kiet10051780 0
Chemie Water Technologies105895322 0
Chemie Water Technologies Pvt Ltd10589538 -0
Magenta Capital Pvt Ltd10708718 -0
Martin Dow Marker Ltd1070940319 21
Martin Dow Ltd107094063 -1
Airport Security Force Foundations108166945 0
Asf Foundation10816690 0
Ibrar Foods10817124 0
Honig Pharmaceutical Laboratories10817330 0
P S G Hospital Pvt Ltd108177838 -0
Raheel Cold Storage108178023 -25
Fetch Sky108181766 -0
Popular Water Tank Pvt Ltd108185214 0
Sindhri Cotton Ginning And Pres Factory1081868243 -9





Table 3: Sorted List from Table 2 with Changes Greater Than or Less Than 10
Name eCIB Q1-24 Q2-24 Change
Popular Water Tank Pvt Ltd1081852140-14
Chemie Water Technologies1058953220-22
Adamjee Engineering Pvt Ltd35533-1-34
P S G Hospital Pvt Ltd1081778380-38
Airport Security Force Foundations1081669450-45
Raheel Cold Storage108178023-25-48
Martin Dow Ltd107094063-1-63
Fetch Sky1081817660-66
Abdul Sattar Noor Muhammad And Co26614140-101
Acme Mills Pvt Ltd3351486-142
A F Ferguson And Co6028-135-163
Agriauto Ind Ltd4421990-199
Sindhri Cotton Ginning And Pres Factory1081868243-9-252
A Majeed And Sons262891-288
Martin Dow Marker Ltd107094031921-299
Formula:
//=SORT(FILTER(F4:J26, (J4:J26 > 10) + (J4:J26 < -10)), 5, -1)
The issue is that since Name and eCIB are derived from a split array using the UNIQUE formula, the SORT function with the FILTER formula in Table 3 cannot use the dynamic range with #. As a result, I have to specify a fixed range. Is there a way to make the range dynamic?
Table 2: Unique Combo IDs Based on Name and eCIB Columns Extracted from Table 1
NameeCIB Q1-24 Q2-24 Change
A Majeed And Sons26289 1 -288
A F Ferguson And Co6028 -135 -163
Abdul Sattar Noor Muhammad And Co266141 40 -101
Acme Mills Pvt Ltd335148 6 -142
Adamjee Engineering Pvt Ltd35533 -1 -34
Agriauto Ind Ltd442199 0 -199
Karachi Institute Of Economics And Technology10051786 -0 -7
Paf Kiet10051780 0 0
Chemie Water Technologies105895322 0 -22
Chemie Water Technologies Pvt Ltd10589538 -0 -8
Magenta Capital Pvt Ltd10708718 -0 -8
Martin Dow Marker Ltd1070940319 21 -299
Martin Dow Ltd107094063 -1 -63
Airport Security Force Foundations108166945 0 -45
Asf Foundation10816690 0 0
Ibrar Foods10817124 0 -4
Honig Pharmaceutical Laboratories10817330 0 0
P S G Hospital Pvt Ltd108177838 -0 -38
Raheel Cold Storage108178023 -25 -48
Fetch Sky108181766 -0 -66
Popular Water Tank Pvt Ltd108185214 0 -14
Sindhri Cotton Ginning And Pres Factory1081868243 -9 -252
-
Formula:
//=UNIQUE(db[Name]:db[eCIB]) //=SUMIFS(db[Q1-24], db[Name], INDEX(F4#, , 1), db[eCIB], INDEX(F4#, , 2)) //=SUMIFS(db[Q2-24], db[Name], INDEX(F28#, , 1), db[eCIB], INDEX(F28#, , 2)) //=I4-H4

 
Upvote 0
How about
Fluff.xlsm
ABCDEFGHIJ
3NameeCIBQ1-24Q2-24
4A Majeed And Sons261890A Majeed And Sons262890-289
5A Majeed And Sons261000A F Ferguson And Co6028-135-163
6A F Ferguson And Co6028-135Abdul Sattar Noor Muhammad And Co26614140-101
7Abdul Sattar Noor Muhammad And Co26614140Acme Mills Pvt Ltd3351486-142
8Acme Mills Pvt Ltd3351486Adamjee Engineering Pvt Ltd35533-1-34
9Adamjee Engineering Pvt Ltd35533-1Agriauto Ind Ltd4421990-199
10Agriauto Ind Ltd4421990Karachi Institute Of Economics And Technology100517860-6
11Karachi Institute Of Economics And Technology100517860Paf Kiet1005178000
12Paf Kiet100517800Chemie Water Technologies1058953220-22
13Chemie Water Technologies1058953220Chemie Water Technologies Pvt Ltd105895380-8
14Chemie Water Technologies Pvt Ltd105895380Magenta Capital Pvt Ltd107087180-8
15Magenta Capital Pvt Ltd107087180Martin Dow Marker Ltd107094031921-298
16Martin Dow Marker Ltd107094031921Martin Dow Ltd107094063-1-64
17Martin Dow Ltd107094063-1Airport Security Force Foundations1081669450-45
18Airport Security Force Foundations1081669450Asf Foundation1081669000
19Asf Foundation108166900Ibrar Foods108171240-4
20Ibrar Foods108171240Honig Pharmaceutical Laboratories1081733000
21Honig Pharmaceutical Laboratories108173300P S G Hospital Pvt Ltd1081778380-38
22P S G Hospital Pvt Ltd1081778380Raheel Cold Storage108178023-25-48
23Raheel Cold Storage108178023-25Fetch Sky1081817660-66
24Fetch Sky1081817660Popular Water Tank Pvt Ltd1081852140-14
25Popular Water Tank Pvt Ltd1081852140Sindhri Cotton Ginning And Pres Factory1081868243-9-252
26Sindhri Cotton Ginning And Pres Factory1081868243-9
27
28
29
30Popular Water Tank Pvt Ltd1081852140-14
31Chemie Water Technologies1058953220-22
32Adamjee Engineering Pvt Ltd35533-1-34
33P S G Hospital Pvt Ltd1081778380-38
34Airport Security Force Foundations1081669450-45
35Raheel Cold Storage108178023-25-48
36Martin Dow Ltd107094063-1-64
37Fetch Sky1081817660-66
38Abdul Sattar Noor Muhammad And Co26614140-101
39Acme Mills Pvt Ltd3351486-142
40A F Ferguson And Co6028-135-163
41Agriauto Ind Ltd4421990-199
42Sindhri Cotton Ginning And Pres Factory1081868243-9-252
43A Majeed And Sons262890-289
44Martin Dow Marker Ltd107094031921-298
Data
Cell Formulas
RangeFormula
F4:G25F4=UNIQUE(db[Name]:db[eCIB])
H4:H25H4=SUMIFS(db[Q1-24], db[Name], INDEX(F4#, , 1), db[eCIB], INDEX(F4#, , 2))
I4:I25I4=SUMIFS(db[Q2-24], db[Name], INDEX(F4#, , 1), db[eCIB], INDEX(F4#, , 2))
J4:J25J4=I4#-H4#
A30:E44A30=SORT(FILTER(F4#:J4#, (J4# > 10) + (J4# < -10)), 5, -1)
Dynamic array formulas.
 
Upvote 0
Solution
Fluff said:
How about
Fluff.xlsm
ABCDEFGHIJ
3NameeCIBQ1-24Q2-24
4A Majeed And Sons261890A Majeed And Sons262890-289
5A Majeed And Sons261000A F Ferguson And Co6028-135-163
6A F Ferguson And Co6028-135Abdul Sattar Noor Muhammad And Co26614140-101
7Abdul Sattar Noor Muhammad And Co26614140Acme Mills Pvt Ltd3351486-142
8Acme Mills Pvt Ltd3351486Adamjee Engineering Pvt Ltd35533-1-34
9Adamjee Engineering Pvt Ltd35533-1Agriauto Ind Ltd4421990-199
10Agriauto Ind Ltd4421990Karachi Institute Of Economics And Technology100517860-6
11Karachi Institute Of Economics And Technology100517860Paf Kiet1005178000
12Paf Kiet100517800Chemie Water Technologies1058953220-22
13Chemie Water Technologies1058953220Chemie Water Technologies Pvt Ltd105895380-8
14Chemie Water Technologies Pvt Ltd105895380Magenta Capital Pvt Ltd107087180-8
15Magenta Capital Pvt Ltd107087180Martin Dow Marker Ltd107094031921-298
16Martin Dow Marker Ltd107094031921Martin Dow Ltd107094063-1-64
17Martin Dow Ltd107094063-1Airport Security Force Foundations1081669450-45
18Airport Security Force Foundations1081669450Asf Foundation1081669000
19Asf Foundation108166900Ibrar Foods108171240-4
20Ibrar Foods108171240Honig Pharmaceutical Laboratories1081733000
21Honig Pharmaceutical Laboratories108173300P S G Hospital Pvt Ltd1081778380-38
22P S G Hospital Pvt Ltd1081778380Raheel Cold Storage108178023-25-48
23Raheel Cold Storage108178023-25Fetch Sky1081817660-66
24Fetch Sky1081817660Popular Water Tank Pvt Ltd1081852140-14
25Popular Water Tank Pvt Ltd1081852140Sindhri Cotton Ginning And Pres Factory1081868243-9-252
26Sindhri Cotton Ginning And Pres Factory1081868243-9
27
28
29
30Popular Water Tank Pvt Ltd1081852140-14
31Chemie Water Technologies1058953220-22
32Adamjee Engineering Pvt Ltd35533-1-34
33P S G Hospital Pvt Ltd1081778380-38
34Airport Security Force Foundations1081669450-45
35Raheel Cold Storage108178023-25-48
36Martin Dow Ltd107094063-1-64
37Fetch Sky1081817660-66
38Abdul Sattar Noor Muhammad And Co26614140-101
39Acme Mills Pvt Ltd3351486-142
40A F Ferguson And Co6028-135-163
41Agriauto Ind Ltd4421990-199
42Sindhri Cotton Ginning And Pres Factory1081868243-9-252
43A Majeed And Sons262890-289
44Martin Dow Marker Ltd107094031921-298
Data
Cell Formulas
RangeFormula
F4:G25F4=UNIQUE(db[Name]:db[eCIB])
H4:H25H4=SUMIFS(db[Q1-24], db[Name], INDEX(F4#, , 1), db[eCIB], INDEX(F4#, , 2))
I4:I25I4=SUMIFS(db[Q2-24], db[Name], INDEX(F4#, , 1), db[eCIB], INDEX(F4#, , 2))
J4:J25J4=I4#-H4#
A30:E44A30=SORT(FILTER(F4#:J4#, (J4# > 10) + (J4# < -10)), 5, -1)
Dynamic array formulas.
Click to expand...
Brother, thanks a lot! But I am using Excel 2021 and had tried exactly the same formula before posting my query here. It was not working and was giving the #VALUE! error, which I guess is due to the split array. Are there any remedies available? Or maybe I am missing something because your response is very easy to understand, and I entered the formula:

=SORT(FILTER(F4#:J4#, (J4# > 10) + (J4# < -10)), 5, -1)
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
VBA to apply Filter accross multiple sheets with the same range
Replies
1
Views
132
Ramadan
R
B
  • Solved
Splitting up an excel file to multiple tabs by "visible" rows.
2
Replies
11
Views
428
BasicUserWithExp
B
S
  • Question Question
VBA - Filter a class array based on class function
Replies
1
Views
269
RoryA
RoryA
B
  • Question Question
Excel 2021 - Building a Pivot Table on a Filter Function Array
Replies
9
Views
1K
BrerRabbit
B
O
  • Question Question
Attempting basic loading of an array: Function "cannot be assigned to an array", Sub "array is fixed or locked"
Replies
4
Views
111
Alex Blakenburg
A

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