autofilter data in excel with formula

[cold]

Hi,

I would like to autofilter data from 1 column with a formula. It is a double filter : greater or equals to 6 OR less or equal to 2.

Column A (time),column B (data)

Column A Column B

8:00 4

8:01 5

8:02 4

8:03 3

8:04 2

8:05 1

8:06 2

8:07 5

8:08 6

The first answer should be 2 and I would like to see the corresponding time: 8:04 automatically.

Thank you

 

[Luke M]

Your use of the word Autofilter is confusing me. If you want to use a true AutoFilter, you could just plug in your two conditions and be done. If you want to generate a new list, you could use the method described here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


=IFERROR(INDEX(A:A, SMALL( IF(($B$2:$B$10 >= 10)+($B$2:$B$10<=2), ROW($B$2:$B$10)), ROW(A1))),"")


Note that this is an array formula, so confirm using Ctrl+Shift+Enter.

 

[SirJB7]

Hi, cold!

Have you tried with the AutoFilter Custom feature?

Form your worksheet, go to Data tab, Sort & Filter grouop, Filter icon (this should add a dropdown filter in each column with data).

Then select filter on column B, Numeric or Numbers Filter, Custom filter and there you can set up two conditions for filtering.

Just advise if any trouble.

Regards!

 

[SirJB7]

@Luke M

Hi!

Sorry, a phone call kept open this topic without pressing enter. I stepped over you.

Regards!

 

[cold]

Hi Luke,

I have no idea how you did it but it is working! I'm so glad I don't have to do macros. I tried the formula you gave me and it is working.

Thank you so much,

Warm regards,

 

[Luke M]

You're welcome, cold.


@SirJB7

No worries, happens to all of us. Always better to have a multitude of solutions than none at all. =)

 

[cold]

Hi again,

I just realized that it is not working perfectly for what I want to do. If I use this example I should get 8:01 instead of 8:04 but it still shows 8:04. Can you please tell me why?

Thank you

8:00 4

8:01 6

8:02 4

8:03 3

8:04 2

8:05 1

8:06 2

8:07 5

8:08 6

 

[cold]

It is ok. I just forgot the Ctrl,Shift, enter...

Thanks again,

Cold

 

[cold]

Hi Luke,

How would you do a second filter after the first one? let's take my first example: filter#1 : >=10 OR <=2. The first result is 8:04. Then how can I filter the rest of the data with this filter: <= -2 or >=4? The result should be 8:07. Thank you.

8:00 4

8:01 5

8:02 4

8:03 3

8:04 2

8:05 1

8:06 2

8:07 5

8:08 6

 

[cold]

Hi Luke,

How would you do a second filter after the first one? let's take my first example: filter#1 : >=10 OR <=2. The first result is 8:04. Then how can I filter the rest of the data with this filter: <= -2 or >=4? The result should be 8:07. Thank you.

8:00 4

8:01 5

8:02 4

8:03 3

8:04 2

8:05 1

8:06 2

8:07 5

8:08 6

 

[Luke M]

Similar formula, just change the conditions:

=IFERROR(INDEX(A:A, SMALL( IF(($B$2:$B$10 >= 4)+($B$2:$B$10<=-2), ROW($B$2:$B$10)), ROW(A1))),"")