Mutiple criteria filtering to separate 2NF table (nested FILTER over FILTER formula?)

C

cerobit

New Member
Joined
Mar 7, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

for our childrens orgranisation we are doing some easy accounting reports. It is sufficient for our purpose, but I am stuck on one partial problem.

On first sheet are input data (financial diary) in 2NF format. So if one cost is "multi-sourced" it is mentioned on multiple lines with same redundant data except the source of financing. This method of entry is satisfactory because of its ease of legibility and comprehensibility:


filter_costs_source_small.xlsx
BCDEFGHIJKLM
1formula based values
2Financial diary
3↓ count of sources of all costs↓ order in case of multi-sourced costs↓ Is it a multi-sourced cost?↓ for conditional visual separator (line) of multi-sourced costs
4
5Operation IDDateItemCostTotal costActionSourceHelper 0 (cost)Helper 1Helper 2Helper 3Helper 4
618010.06.2022Material (gas)-278,00 Action 1Source C278,0010 YES
718111.06.2022Material (building)-1 900,00 Action 1Source B5 083,0031YES 
818111.06.2022Material (building)-2 100,00 Action 1Source C5 083,0032YES 
918111.06.2022Material (building)-1 083,00-5 083,00Action 1Source D5 083,0033YESYES
1018416.06.2022Material (saw blade)-828,00 Action 1Source B828,0010  
1120029.06.2022Material (screws)-162,00 Action 1Source C162,0010 YES
1221230.06.2022Material (steel pot)-2 258,00 Action 1Source C4 258,0021YES 
1321230.06.2022Material (steel pot)-2 000,00-4 258,00Action 1Source D4 258,0022YESYES
1421411.07.2022Food-952,00 Action 1Source B952,0010 YES
1521511.07.2022Material (building)-1 100,00 Action 1Source B2 674,0021YES 
1621511.07.2022Material (building)-1 574,00-2 674,00Action 1Source C2 674,0022YESYES
1721611.07.2022Material (fabrics)-755,00 Action 1Source C755,0010 YES
1821814.07.2022Material (plasters)-580,00 Action 1Source B1 080,0021YES 
1921814.07.2022Material (plasters)-500,00-1 080,00Action 1Source D1 080,0022YESYES
2022015.07.2022Services (health)-250,00 Action 1Source B250,0010  
2122718.07.2022Material (pharmacy)-198,00 Action 1Source C198,0010 YES
2222818.07.2022Food-909,00 Action 1Source B1 109,0021YES 
2322818.07.2022Food-200,00-1 109,00Action 1Source C1 109,0022YESYES
2423020.07.2022Services (waste fee)-300,00 Action 1Source B300,0010  
2523824.07.2022Material (organizer, rail)-957,00 Action 1Source C957,0010 YES
2623925.07.2022Transportation (cars)-1 100,00 Action 1Source B3 542,0031YES 
2723925.07.2022Transportation (cars)-900,00 Action 1Source C3 542,0032YES 
2823925.07.2022Transportation (cars)-1 542,00-3 542,00Action 1Source D3 542,0033YESYES
2924025.07.2022Admission (swimming pool)-886,00 Action 1Source B886,0010  
3024127.07.2022Material (markers)-33,00 Action 1Source D33,0010  
3124930.07.2022Services (water supply)-160,00 Action 1Source B160,0010 YES
3233931.08.2022Postal service-433,00 Action 2Source A437,0021YES 
3333931.08.2022Postal service-4,00-437,00Action 2Source B437,0022YESYES
3434304.09.2022Material (creative)-160,00 Action 2Source A160,0010  
3542027.10.2022Entrance fee (exhibition)-1 140,00 Action 2Source C1 140,0010 YES
3642228.10.2022Transport (bus)-200,00 Action 2Source A890,0031YES 
3742228.10.2022Transport (bus)-500,00 Action 2Source B890,0032YES 
3842228.10.2022Transport (bus)-190,00-890,00Action 2Source C890,0033YESYES
3942328.10.2022Services (hospital)-80,00 Action 2Source A80,0010  
4042628.10.2022Entrance fee (swimming pool)-1 670,00 Action 2Source C1 670,0010 YES
4142829.10.2022Food-1 775,00 Action 2Source A2 775,0021YES 
4242829.10.2022Food-1 000,00-2 775,00Action 2Source B2 775,0022YESYES
4342930.10.2022Food-460,00 Action 2Source A460,0010  
4443301.11.2022Transport (train)-274,00 Action 2Source A274,0010 YES
4554008.12.2022Accommodation-2 000,00 Action 2Source B6 600,0021YES 
4654008.12.2022Accommodation-4 600,00-6 600,00Action 2Source C6 600,0022YESYES
4754409.12.2022Administrative fee-300,00 Action 2Source B300,0010  
Data (input)
Cell Formulas
RangeFormula
I6:I47I6=SUMIF([Operation ID], [@[Operation ID]], [Cost]) * -1
J6:J47J6=COUNTIFS([Operation ID], [@[Operation ID]])
K6:K47K6=IF([@[Helper 1]] > 1, COUNTIF(data[[#Headers],[Operation ID]]:[@[Operation ID]], [@[Operation ID]]), 0)
L6:L47L6=IF([@[Helper 2]] > 0, "YES", "")
M6:M47M6=IF(OR([@[Helper 3]] <> OFFSET([@[Helper 3]], 1, 0), AND([@[Helper 2]] > OFFSET([@[Helper 2]], -1, 0), OFFSET([@[Helper 2]], -1, 1) <> 0, [@[Helper 2]] > OFFSET([@[Helper 2]], 1, 0))), "YES", "")
F6:F47F6=IF(AND([@[Helper 4]] = "YES", [@[Helper 3]] = "YES"), SUMIF([Operation ID], [@[Operation ID]], [Cost]), "")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E6:E47Expression=$L6 = ""textNO
B6:M47Expression=$M6 <> ""textNO



On the second sheet is the desired overview of actions and its costs by sources:

filter_costs_source_small.xlsx
JKLMNO
3Overview of costs by source (desired form of output)
4Action 1 (sample proper solution)
5...of which covered by
6DateItemTotal cost…Source BSource CSource D
710.06.2022Material (gas)278,00278,00
811.06.2022Material (building)5 083,001 900,002 100,001 083,00
916.06.2022Material (saw blade)828,00828,00
1029.06.2022Material (screws)162,00162,00
1130.06.2022Material (steel pot)4 258,002 258,002 000,00
1211.07.2022Food952,00952,00
1311.07.2022Material (building)2 674,001 100,001 574,00
1411.07.2022Material (fabrics)755,00755,00
1514.07.2022Material (plasters)1 080,00580,00500,00
1615.07.2022Services (health)250,00250,00
1718.07.2022Material (pharmacy)198,00198,00
1818.07.2022Food1 109,00909,00200,00
1920.07.2022Services (waste fee)300,00300,00
2024.07.2022Material (organizer, rail)957,00957,00
2125.07.2022Transportation (cars)3 542,001 100,00900,001 542,00
2225.07.2022Admission (swimming pool)886,00886,00
2327.07.2022Material (markers)33,0033,00
2430.07.2022Services (water supply)160,00160,00
25
26Action 2 (sample proper solution)
27...of which covered by
28DateItemTotal cost…Source ASource BSource C
2931.08.2022Postal service437,00433,004,00
3004.09.2022Material (creative)160,00160,00
3127.10.2022Entrance fee (exhibition)1 140,001 140,00
3228.10.2022Transport (bus)890,00200,00500,00190,00
3328.10.2022Services (hospital)80,0080,00
3428.10.2022Entrance fee (swimming pool)1 670,001 670,00
3529.10.2022Food2 775,001 775,001 000,00
3630.10.2022Food460,00460,00
3701.11.2022Transport (train)274,00274,00
3808.12.2022Accommodation6 600,002 000,004 600,00
3909.12.2022Administrative fee300,00300,00
Overview (output)




I cannot figure out, how to filter costs by its source, more precisely how to display its value in a proper position when split by source.

I have tried to implement these rules in FILTER function:


A) Is it a "single source" cost AND is in current column/source? → put one row and whole cost

Excel Formula: 
(data[Source] = source_filter) * (data[Helper 3] = "")




B) Is it a "single source" cost AND is in different column/source? → put one row and whole cost

Excel Formula: 
(data[Source] <> source_filter) * (data[Helper 3] = "")




C) Is it a "multi-source" cost AND in current column/source? → skip other rows and put its partial cost

Excel Formula: 
(data[Source] = source_filter) * (data[Helper 3] = "YES")




D) Is it a "multi-source" cost AND in different column/source? → skip other rows and put its partial cost

Excel Formula: 
(data[Source] <> source_filter) * (data[Helper 3] = "YES") * (data[Helper 4] = "YES")


Those two last rules are not working well. Problem is I would need to "pre-filter" the data, that comes to FILTER function, so the output would skip the empty lines correctly. Like add a rule something like "IF it is a multi-sourced cost AND it is in a current column/source AND has not been mentioned yet"...:

filter_costs_source_small.xlsx
BCDEFGH
1Choose action→Action 1
2
3Overview of costs by source (my best attempt so far)
4Action 1
5...of which covered by
6DateItemTotal cost…Source BSource CSource D 
710.06.2022Material (gas)278,00 278,00  
811.06.2022Material (building)5 083,001 900,002 100,001 083,00
916.06.2022Material (saw blade)828,00
1029.06.2022Material (screws)162,00828,00
1130.06.2022Material (steel pot)4 258,00162,002 000,00
1211.07.2022Food952,002 258,00
1311.07.2022Material (building)2 674,00952,00
1411.07.2022Material (fabrics)755,001 100,00
1514.07.2022Material (plasters)1 080,001 574,00500,00
1615.07.2022Services (health)250,00755,00
1718.07.2022Material (pharmacy)198,00580,00
1818.07.2022Food1 109,00
1920.07.2022Services (waste fee)300,00250,00198,00
2024.07.2022Material (organizer, rail)957,00200,00
2125.07.2022Transportation (cars)3 542,00909,001 542,00
2225.07.2022Admission (swimming pool)886,00957,00
2327.07.2022Material (markers)33,00300,00900,0033,00
2430.07.2022Services (water supply)160,00
251 100,00
26
27886,00
28
29160,00
Overview (output)
Cell Formulas
RangeFormula
B4B4=action_choice
E6E6=IFERROR(INDEX(SORT(UNIQUE(FILTER(data[Source], data[Action] = action_choice))), 1), "")
F6F6=IFERROR(INDEX(SORT(UNIQUE(FILTER(data[Source], data[Action] = action_choice))), 2), "")
G6G6=IFERROR(INDEX(SORT(UNIQUE(FILTER(data[Source], data[Action] = action_choice))), 3), "")
H6H6=IFERROR(INDEX(SORT(UNIQUE(FILTER(data[Source], data[Action] = action_choice))), 4), "")
B7:B24B7=FILTER(data[Date], (((data[Helper 4] = "YES") * (data[Helper 3] = "YES")) + (data[Helper 2] = 0)) * (data[Action] = action_choice))
C7:C24C7=FILTER(data[Item], (((data[Helper 4] = "YES") * (data[Helper 3] = "YES")) + (data[Helper 2] = 0)) * (data[Action] = action_choice))
D7:D24D7=FILTER(data[Helper 0 (cost)], (((data[Helper 4] = "YES") * (data[Helper 3] = "YES")) + (data[Helper 2] = 0)) * (data[Action] = action_choice))
E7:E29,G7:G24,F7:F27E7=LET( source_filter, E6, FILTER(IF(data[Source] = source_filter, data[Cost] * -1, ""), (data[Action] = action_choice) * ( ((data[Source] = source_filter) * (data[Helper 3] = "")) + ((data[Source] <> source_filter) * (data[Helper 3] = "")) + ((data[Source] = source_filter) * (data[Helper 3] = "YES")) + ((data[Source] <> source_filter) * (data[Helper 3] = "YES") * (data[Helper 4] = "YES") ) ) ) )
H7:H24H7=LET( source_filter, H6, FILTER(IF(data[Source] = source_filter, data[Cost] * -1, ""), (data[Action] = "Action 1") * ( ((data[Source] = source_filter) * (data[Helper 3] = "")) + ((data[Source] <> source_filter) * (data[Helper 3] = "")) + ((data[Source] = source_filter) * (data[Helper 3] = "YES")) + ((data[Source] <> source_filter) * (data[Helper 3] = "YES") * (data[Helper 4] = "YES") ) ) ) )
Dynamic array formulas.
Named Ranges
NameRefers ToCells
action_choice='Overview (output)'!$C$1H6, F6:G7, B7:E7, E6
Cells with Data Validation
CellAllowCriteria
C1ListAction 1;Action 2



For a better view of the data you can download the whole table here:

ulozto.net

filter_costs_source.xlsx

ulozto.net ulozto.net


Notes:
  1. I prefer spilled dynamic output and formulas due to several further reasons.
  2. Filter function should be universal in the sense of independence from the source.
  3. I can add some additional helping colums to data table, if needed. But I would rather not make any "source-sutomized" colums (like extra column for every source), if possible...
  4. Every cost can be financed by up to 4 sources.
  5. Number of different sources over the whole year is about 20, it varies over the actions.


Thank you for your ideas.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
How about
filter_costs_source.xlsx
ABCDEFGH
1Choose action→Action 1
2
3Overview of costs by source (my best attempt so far)
4Action 1
5...of which covered by
6DateItemTotal cost…Source BSource CSource D 
717704/06/2022Material (screws)209.000.00209.000.00
817810/06/2022Food47.0047.000.000.00
918010/06/2022Material (gas)278.000.00278.000.00
1018111/06/2022Material (building)5,083.001,900.002,100.001,083.00
1118416/06/2022Material (saw blade)828.00828.000.000.00
1220029/06/2022Material (screws)162.000.00162.000.00
1321230/06/2022Material (steel pot)4,258.000.002,258.002,000.00
1421411/07/2022Food952.00952.000.000.00
1521511/07/2022Material (building)2,674.001,100.001,574.000.00
1621611/07/2022Material (fabrics)755.000.00755.000.00
1721711/07/2022Material (modeling)106.000.00106.000.00
18218.0014/07/2022Material (plasters)1,080.00580.000.00500.00
19220.0015/07/2022Services (health)250.00250.000.000.00
20222.0016/07/2022Material (building)2,202.000.002,202.000.00
21223.0017/07/2022Material (batteries)534.000.000.00534.00
22224.0018/07/2022Material (drugstores)120.000.000.00120.00
23227.0018/07/2022Material (pharmacy)198.000.00198.000.00
24228.0018/07/2022Food1,109.00909.00200.000.00
25229.0018/07/2022Material (stationery)96.000.0096.000.00
26230.0020/07/2022Services (waste fee)300.00300.000.000.00
27231.0020/07/2022Material (kitchen)197.000.00197.000.00
28232.0020/07/2022Food228.00228.000.000.00
29233.0021/07/2022Material (medicines)195.00195.000.000.00
30234.0021/07/2022Material (paints)928.000.00928.000.00
31235.0021/07/2022Food255.00255.000.000.00
32237.0022/07/2022Material (towels)1,180.000.001,180.000.00
33238.0024/07/2022Material (organizer, rail)957.000.00957.000.00
34239.0025/07/2022Transportation (cars)3,542.001,100.00900.001,542.00
35240.0025/07/2022Admission (swimming pool)886.00886.000.000.00
36241.0027/07/2022Material (markers)33.000.000.0033.00
37244.0027/07/2022Material (tape)699.000.00699.000.00
38245.0029/07/2022Material (tools)479.000.00479.000.00
39248.0029/07/2022Food519.00519.000.000.00
40249.0030/07/2022Services (water supply)160.00160.000.000.00
41
Overview (output)
Cell Formulas
RangeFormula
B4B4=action_choice
E6E6=IFERROR(INDEX(SORT(UNIQUE(FILTER(data[Source], data[Action] = action_choice))), 1), "")
F6F6=IFERROR(INDEX(SORT(UNIQUE(FILTER(data[Source], data[Action] = action_choice))), 2), "")
G6G6=IFERROR(INDEX(SORT(UNIQUE(FILTER(data[Source], data[Action] = action_choice))), 3), "")
H6H6=IFERROR(INDEX(SORT(UNIQUE(FILTER(data[Source], data[Action] = action_choice))), 4), "")
A7:C40A7=UNIQUE(FILTER(data[[Operation ID]:[Item]],data[Action]=C1))
D7:D40D7=ABS(SUMIFS(data[Cost],data[Operation ID],INDEX(A7#,,1)))
E7:E40E7=ABS(SUMIFS(data[Cost],data[Operation ID],INDEX(A7#,,1),data[Source],E6))
F7:F40F7=ABS(SUMIFS(data[Cost],data[Operation ID],INDEX(A7#,,1),data[Source],F6))
G7:G40G7=ABS(SUMIFS(data[Cost],data[Operation ID],INDEX(A7#,,1),data[Source],G6))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
action_choice='Overview (output)'!$C$1E6:H6, A7
Cells with Data Validation
CellAllowCriteria
C1ListAction 1,Action 2
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Is It A Problem to Have Your Table Contain Reference Cells?
Replies
5
Views
267
My Aswer Is This
My Aswer Is This
T
  • Solved
SUMPRODUCT formula not working
Replies
5
Views
250
thewiseguy
T
GMC The Macro Man
  • Question Question
SEQUENCE formula
Replies
0
Views
144
GMC The Macro Man
GMC The Macro Man
JenniferMurphy
  • Question Question
I could use help with a bit of a complex table addressing problem
Replies
1
Views
231
JenniferMurphy
JenniferMurphy
P
  • Solved
Filter Formula Issues
2
Replies
10
Views
463
Fluff
Fluff

Forum statistics

Threads
1,224,813
Messages
6,181,117
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