Excel filter multiple values

Question

I have a datasheet with 1300 rows. Each row is numbered by a specific number linked to our database. I need to filter everything by two arguments 5 times.

• First one: filter everything ending on 0 and 1
• Second filter: filer everything ending on 2 and 3
• etc
• etc.
• fifth filter: filter everything ending on 8 and 9

I need to use these filters seperately because different people will use it.

example: person 1 will need to see data with the ending numbers 2 and 3 person 2 will need to see data with the ending numbers 6 and 7

I can filter the data with one argument like *5 or *8 but when I try to use 2 arguments divided by "", OR, AND it won't work.

Advanced filtering also doesn't want to use my two arguments. When I put these 2 criteria in different cells and select them as criteria. The result will be blank. And when I only select one criteria like *5, the filter will make no sense because it will also show cells ending with other numbers.

I expect the result to be a formula I can use in my advanced filter or a VBA code as I will be implementing it in a small script to make it as easy as possible to use for my colleagues. And an explanaition would be much appreciated so others and me can implement it in a different way.

Answer 1

Indirect (workaround) solution

Since you have real numbers in the column you want to filter at, you cannot use text filters with asterisks * and must use number filters. Therfore you would need to use a helper column for filtering, that shows only the last digit of your numbers.

Add the following formula to your helper column:

=VALUE(RIGHT(A:A,1))

So it looks like in the picture below.

^{Image 1: Added helper column showing the ending digit only.}

Now you can filter for the exact ending numbers 1 or 2 (using xlOr),

.AutoFilter Field:=2, Criteria1:="=1", Operator:=xlOr, Criteria2:="=2"

or even on ending ranges like 1-5 by using the numeric in between filter >=1 and <=5 with xlAnd.

.AutoFilter Field:=2, Criteria1:=">=1", Operator:=xlAnd, Criteria2:="<=5"

^{Image 2: Data filtered by ending digits, showing only ending digits from 1 to 5.}