# Can my SORT & FILTER formula be rewritten to work the same without them?

#### gravanoc

##### Active Member
I'm sure I've seen this somewhere, but my Google searches keep pulling the wrong results. Here's my formula:

Excel Formula:
``=SORT(FILTER(IF(Dashboard!\$B\$3>Dashboard[Next Interest],Dashboard[Source.Name],""),(IF(Dashboard!\$B\$3>Dashboard[Next Interest],Dashboard[Source.Name],"")<>"")*(IF(Dashboard!\$B\$3>Dashboard[Next Interest],Dashboard[Source.Name],"")<>0)))``

Simply checks that the current time via NOW() is greater than some other time, and if so pulls the data from that table column, sorts it, and filters out any blanks or zeros. Preference is to do this without VBA.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try to adopt the following example, which uses today's day as the criteria for simplicity . . .

A1:F6

 Name Date Criteria Count Results John 10/18/21​ 1/08/22​ 3​ Barbara Abbey 2/28/22​ John Zach 12/15/21​ Zach Bob 1/13/22​ Barbara 11/30/21​

D2:

VBA Code:
``=TODAY()``

E2:

VBA Code:
``=COUNTIF(B2:B6,"<"&D2)``

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

VBA Code:
``=IF(ROWS(F\$2:F2)<=\$E\$2,INDEX(\$A\$2:\$A\$6,MATCH(SMALL(IF(\$B\$2:\$B\$6<\$D\$2,COUNTIF(\$A\$2:\$A\$6,"<"&\$A\$2:\$A\$6)),ROWS(F\$2:F2)),IF(\$B\$2:\$B\$6<\$D\$2,COUNTIF(\$A\$2:\$A\$6,"<"&\$A\$2:\$A\$6)),0)),"")``

Hope this helps!

Actually, you did say that your data contains blanks . . .

A1:F11

 Name Date Criteria Count Results John 10/18/21​ 1/09/22​ 3​ Barbara John Zach Abbey 2/28/22​ Zach 12/15/21​ Bob 1/13/22​ Barbara 11/30/21​

D2:

VBA Code:
``=TODAY()``

E2:

VBA Code:
``=COUNTIF(B2:B11,"<"&D2)``

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

VBA Code:
``=IF(ROWS(F\$2:F2)<=\$E\$2,INDEX(\$A\$2:\$A\$11,MATCH(SMALL(IF(\$B\$2:\$B\$11<>"",IF(\$B\$2:\$B\$11<\$D\$2,COUNTIF(\$A\$2:\$A\$11,"<"&\$A\$2:\$A\$11))),ROWS(F\$2:F2)),IF(\$B\$2:\$B\$11<>"",IF(\$B\$2:\$B\$11<\$D\$2,COUNTIF(\$A\$2:\$A\$11,"<"&\$A\$2:\$A\$11))),0)),"")``

Replies
3
Views
634
Replies
25
Views
1K
Replies
18
Views
540
Replies
14
Views
817
Replies
0
Views
1K

1,196,297
Messages
6,014,530
Members
441,826
Latest member
roudarreza

### 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.

### Which adblocker are you using?

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

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