Excel - Generate list that displays cells that meet multiple forms of criteria

user_11137105 0
Viewed 93

I've attempted to dig through this online but surprisingly I can't find any solution to what I'm trying to do within Excel in an attempt to automate a series of tasks.

I have a list/column within excel that contains both numbers and letters, similar to this:

OriginalList
5682se
5385ba
5682aw
4444ss
4444we
8888aa

I currently have a column directly next to it, which displays Yes/No properties if the cells meet the criteria.

=IF(COUNTIF(F5, "44*"),"yes","no")

But this will no longer work as I now have multiple criteria requirements, (Cells that BEGIN with: 44,88,56)

At this point, I would prefer to simply generate a new list the ONLY displays the items that meet ONE of the forms of criteria, instead of what I was doing in the past. Keep in mind, I'm having to avoid generic Excel filters and need this to be performed within a formula. VBA solutions are welcome as well. Example of what I'm trying to do at this point:

OriginalList     NewList  
5682se           5682se
5385ba           4444ss
5682aw           4444we
4444ss           8888aa
4444we          
8888aa

I've managed to get a count formula to work properly, with the same criteria I'm looking for, I've tried adjusting this to create a new Column list, but to no avail:

=COUNTIF($D$2:$D$8000,"44*")+COUNTIF($D$2:$D$8000,"88*")+COUNTIF($D$2:$D$8000,"56")
office365 excel-formula vba excel
2 Answers

Here is an alternative using Microsoft365:

enter image description here

Formula in B2:

=FILTER(A2:A7,MMULT(--(--LEFT(A2:A7,2)={44;88;56}),{1,1,1}))

Or maybe rather use MATCH():

=FILTER(A2:A7,ISNUMBER(MATCH(--LEFT(A2:A7,2),{44,56,88},0)))

I hope I didn't get lost in translation there with comma's and semi-colon.

user_97581940

If you have Excel365 then try-

=LET(x,A2:A7,FILTER(x,(LEFT(x,2)="44")+(LEFT(x,2)="56")+(LEFT(x,2)="88")))
user_55147470
Related