How to Automate filters

arniebun

New Member
Joined
Jun 11, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
so I have been using this function in excel =IF(AND(Y82<0%,U82<0,H82<0,L82<0,Z82>0%),$AD$28,"No com") so my sheet consists of different columns of data and I have a main Colum which has results that will total in a cell like, 50% 40% when i filter a number of different columns of data, either plus, minus, or between certain values like -1% to -2% and 1% to 2% and a set of different variations of this.

so i can have column 1 great than 0, 2 less than 0, 3 more than 0, and 4 between 1% and 2%, lets say give me a result of 65%.

I have like 300 of these things to work out. I am not asking for a solution (but one would be much welcome), all i want is to know where to look to learn how to do this.
a specific name would be great so i can go on google and find a learning resource of how to do it. I know pyton may do this but I want to know what I have to specifically search for because I don't want to have to learn all of lets say pyexcel.

what I want to do is write a program to automate this process, so I would set the different Colum possibilities set a target % in the results column and it will try every possibilities to see what would sit between 66% to 100% in the results column.

examle below

261​
COUNT
301​
COUNT
301​
COUNT
562​
COUNT
562​
300​
POS
140​
POS
146​
POS
261​
POS
260​
46.44%​
NEG
161​
NEG
154​
NEG
300​
NEG
298​
53.38%​
my targets %POS
46.51%​
%POS
48.50%​
%POS
46.44%​
%POS
46.26%​
%NEG
53.49%​
5NEG
51.16%​
%NEG
53.38%​
5NEG
53.02%​
>0<0
fliterfilterfilterfilterfilterfilter high or low
1​
results 1results 2
EFGHIJKLMNOPQRSTUVWXYZAAABACday 1
Column21Column22Column61Column62Column63Column64Column65Column66Column67Column68Column69Column71Column72Column73Column74Column75Column76Column77Column78Column79Column80Column81Column82Column83Column84Column1
#VALUE!​
36461.44​
0.000%​
75741.00​
0​
0​
0​
0​
0​
0.000%​
0.000%​
0.000%​
0.000%​
0.000%​
-1.770%​
-4.003%​
1.270%​
Winner
#VALUE!​
36461.44​
0.000%​
75741.00​
2.6​
-26.9​
-1.4​
-26​
4​
-0.9​
0.000%​
0.000%​
0.000%​
0.000%​
0.000%​
-2.248%​
0.589%​
1.270%​
-5.642%​
-2.779%​
No Trade
0.443%​
36299.86​
0.000%​
75741.00​
0.5​
-12.5​
-1.5​
-17.9​
2​
5.4​
2.376%​
-1.078%​
-2.248%​
-3.454%​
0.589%​
-3.326%​
-2.864%​
-2.779%​
-4.508%​
-0.915%​
No Trade
0.443%​
36299.86​
0.000%​
75741.00​
0.6​
-20.6​
-2​
-14.9​
2.6​
-5.7​
1.561%​
1.479%​
-3.326%​
-0.082%​
-2.864%​
-1.847%​
-2.946%​
-0.915%​
-3.093%​
-1.597%​
No Trade
0.443%​
36299.86​
0.000%​
75741.00​
0.1​
-23.6​
-0.9​
-4.7​
1​
-18.9​
-4.169%​
-3.238%​
-1.847%​
0.931%​
-2.946%​
-5.084%​
-2.015%​
-1.597%​
-1.171%​
-0.460%​
No Trade
0.443%​
36299.86​
0.000%​
75741.00​
0.7​
-26.8​
0.3​
-0.7​
0.4​
-26.1​
2.837%​
6.276%​
-5.084%​
3.439%​
-2.015%​
1.191%​
1.424%​
-0.460%​
0.026%​
-1.610%​
No Trade
0.443%​
36299.86​
0.000%​
75741.00​
-4.6​
-28.9​
1.3​
11.5​
-5.9​
-40.4​
1.596%​
2.727%​
1.191%​
1.131%​
1.424%​
3.919%​
2.555%​
-1.610%​
1.133%​
0.554%​
No Trade
0.443%​
36299.86​
0.000%​
75741.00​
-3.9​
-26.5​
1.6​
12.5​
-5.5​
-39​
-3.209%​
-6.110%​
3.919%​
-2.901%​
2.555%​
-2.191%​
-0.345%​
0.554%​
-1.691%​
0.355%​
No Trade
0.443%​
36299.86​
0.000%​
75741.00​
-4.3​
-30.5​
2.3​
15.5​
-6.6​
-46​
5.229%​
7.358%​
-2.191%​
2.129%​
-0.345%​
5.167%​
1.784%​
0.355%​
0.000%​
0.746%​
No Trade
0.443%​
36299.86​
0.000%​
75741.00​
-4.7​
-27.9​
2.4​
15.6​
-7.1​
-43.5​
-4.927%​
-4.339%​
5.167%​
0.587%​
1.784%​
0.827%​
2.371%​
0.746%​
-2.369%​
-0.521%​
No Trade
0.443%​
36299.86​
0.000%​
75741.00​
-3.4​
-30.6​
1.6​
19.4​
-5​
-50​
0.309%​
-0.725%​
0.827%​
-1.034%​
2.371%​
0.102%​
1.337%​
-0.521%​
-0.293%​
-2.254%​
No Trade
0.443%​
36299.86​
213.603%​
-86044.00​
-5.6​
-37​
1.9​
14.7​
-7.5​
-51.7​
2.789%​
1.444%​
0.102%​
-1.346%​
1.337%​
1.546%​
-0.009%​
-2.254%​
-0.279%​
2.081%​
No Trade
0.443%​
36299.86​
213.603%​
-86044.00​
-4.1​
-42.6​
1.4​
22.7​
-5.5​
-65.3​
-1.489%​
-0.430%​
1.546%​
1.059%​
-0.009%​
1.116%​
1.050%​
2.081%​
-2.363%​
-1.641%​
No Trade
0.443%​
36299.86​
213.603%​
-86044.00​
-4.3​
-48.1​
2.3​
19.1​
-6.6​
-67.2​
-0.176%​
-0.523%​
1.116%​
-0.347%​
1.050%​
0.593%​
0.703%​
-1.641%​
-5.450%​
1.594%​
No Trade
39.053%​
22123.66​
213.603%​
-86044.00​
-3.8​
-39.6​
3.1​
20.5​
-6.9​
-60.1​
-3.188%​
-1.808%​
0.593%​
1.379%​
0.703%​
-1.215%​
2.082%​
1.594%​
-7.610%​
-2.240%​
No Trade
39.053%​
22123.66​
213.603%​
-86044.00​
-5.3​
-33.9​
2.2​
20.5​
-7.5​
-54.4​
2.865%​
0.535%​
-1.215%​
-2.330%​
2.082%​
-0.681%​
-0.248%​
-2.240%​
-5.961%​
-0.053%​
No Trade
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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