CODEWITHSUNDEEP
excel
TheHoff
TheHoff

Reputation: 15

Filtering values with a formula

I'm trying to create a formula that will filter out any values that do not meet my criteria, without using an actual filter. I've been searching and experimenting a lot, but to no avail.

I think an array formula will do the trick but I'm not familair enough with those to get the results I want: If the criterium is to filter values that end with "1", I want a column that only displays those values, if necessary with the help of an additional column.

  |A        |B        |C
 -|---------|---------|--------
 1|AValue1  |1        |AValue1
 2|AValue2  |0        |BValue1
 3|AValue3  |0        |
 4|BValue1  |1        |Where 1 means the value meets my criteria and 0 means it doesn't.
 5|BValue2  |0        |
 6|BValue3  |0        |

The idea is to be able to paste anything in column A and get filtered results in one of the next columns.

Can anyone help me?

Upvotes: 0

Views: 100

Answers (1)

Axel Richter
Axel Richter

Reputation: 61870

This is possible directly with the following array formula:

{=INDEX($A$1:$A$10000,SMALL(IF(RIGHT($A$1:$A$10000,1)="1",ROW($A$1:$A$10000)),ROW(A1)))}

Or with a helper column B as in your example faster without array formula:

in B

=IF(RIGHT(A1,1)="1",ROW())

in C

=INDEX(A:A,SMALL(B:B,ROW(A1)))

The approach is: Get the row numbers of the values in column A which matches the requirements. Then use INDEX to get this rows from column A in order from smallest to highest.

enter image description here

Upvotes: 1

Related Questions