UNIQUE FILTER formula with multiple criteria

freeb1893

Board Regular
Joined
Jul 30, 2012
Messages
234
Office Version
  1. 365
Platform
  1. Windows
So I already have the following formula:

=UNIQUE(FILTER(AI2:AI1500,(AI2:AI1500<>"")*(ISNUMBER(SEARCH(",",AI2:AI1500)))))

This indexes me a list of unique values from Array AI2:AI1500 where the value is not blank and contains a comma.

I want to further enhance this formula and further limit my resulting list of unique values, by also looking at another adjacent array AM2:AM1500 (which is a list of dates) and only returning a unique value if the value on the same row in Array AM2:AM1500 is within the last 60 days,

Anyone know how to tweak the above formula to include this additonal criteria?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try this:
Excel Formula:
=UNIQUE(FILTER(AI2:AI1500,(AI2:AI1500<>"")*(ISNUMBER(SEARCH(",",AI2:AI1500)))*(AM2:AM1500>=(TODAY()-60))))
 
Upvote 0
This indexes me a list of unique values from Array AI2:AI1500 where the value is not blank and contains a comma.
The check for non-blanks in column AI is pointless since if the cell has to contain a comma, that already establishes that it will not be blank.

if the value on the same row in Array AM2:AM1500 is within the last 60 days,
It is not clear from that whether column AM might contain future dates or not. So one of these may do what you want.
Excel Formula:
=UNIQUE(FILTER(AI2:AI1500,ISNUMBER(FIND(",",AI2:AI1500))*(AM2:AM1500>=(TODAY()-60)),""))
Excel Formula:
=UNIQUE(FILTER(AI2:AI1500,ISNUMBER(FIND(",",AI2:AI1500))*(AM2:AM1500>=(TODAY()-60))*(AM2:AM1500<=TODAY()),""))
 
Upvote 0
Try this:
Excel Formula:
=UNIQUE(FILTER(AI2:AI1500,(AI2:AI1500<>"")*(ISNUMBER(SEARCH(",",AI2:AI1500)))*(AM2:AM1500>=(TODAY()-60))))
That works! Thank you so much! you're the best!
 
Upvote 0
You're welcome...we're happy to help. Peter's point is not lost on me, and you may want to look at his suggestions. The first of those is similar to mine except it omits the requirement for the cells to be "not blank". That's okay because your original formula required cells to contain a comma--which means those cells are not blank, therefore the first filtering criterion (<>"") is not needed.
 
Upvote 0
Makes sense! I think the blank search was my original logic, then I realized I needed the comma search logic, but didn't remove the blank search after adding the comma search logic, since it didn't break anything, and didn't factor in that the comma logic covers both
 
Upvote 0

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

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