0

I'm wondering if there is a way to have a range of numbers in a cell, type in the number at the top and filter any cells that don't have the number in that range.

ex. I type 132 cells below have ranges 110-150, 90-98 and 130-134

I want the rows with 110-150 and 130-134 to show and 90-98 to be hidden. but I still want to see the ranges.

sorry I have very elementary excel skills.

  • The specifics of the result you want are fuzzy. You could turn on filtering and that would create a pull-down menu with the unique values contained in the column. You could select which ranges you want displayed or hidden. The hidden ranges would have their entire row hidden. To change the selection, you pull down the menu again and change your choices. That's trivial and built in. If the requirement is that you type something in another cell to indicate what you want to show or hide, you can do that. (cont'd) – fixer1234 Jun 29 at 19:46
  • Besides replicating what's already built into the filters, you would need formulas and functions to compare what you enter to the values in the cells. If you type a single, exactly-matching range, that's straightforward. If you enter only part of the range, like 130 to represent 130-134, matching would be more complicated. If you type multiple ranges, matching would be more complicated. Now you describe hiding certain ranges but still seeing them. I have no idea what you're envisioning there. Can you mock up a simple before and after example to illustrate what you have in mind? – fixer1234 Jun 29 at 19:46
  • Welcome to Super User,, my simple suggestion is to edit the post & add some sample data for better understanding, since your query is bit confusing,, by finding 132 , you are trying to filter rows with 110-150 and 130-134 to show and 90-98 to be hidden. are they like 90, 91 to 98 or 90-98 !! – Rajesh S Jun 30 at 6:19
0

It's okay, everybody has to start from somewhere. One thing I'd like to point out is that excel, whilst providing a relatively easy way to setup 'rules', does not offer automation as readily. You can create a new column which indicates when eg the number is in range, but it must be separate to preserve the input, quite possibly using intermediate columns, and then you'd have to apply the filter manually (unless venturing deeper into the realm of vba).

Assuming the range values are in column B, we use the following formulas:

D2=FIND("-",B2)
E2=VALUE(LEFT(B2,D2-1))
F2=VALUE(MID(B2,D2+1,99))
G1={Desired value, eg 132}
G2=IF(AND($G$1>=E2,$G$1<=F2),1,0)

In D, E and F we separate the range to something more malleable - D locates the the separator hyphen, E and F cut the range up until and starting from the separator respectively, to receive the start / end values (converted to numbers). We're using G1 as an input cell per the requirement, though sometimes it could be cleaner to place all inputs in a single column or sheet (depending on how many you have); then the rest of G is a "between" condition. N is between LB and UB <=> N is bigger than LB and less than UB. Finally, autofilter according to G = 1, and you have the desired rows.

This is the result:

Before filtering Filtered

Now, what you can do with excel is based on knowing more programmatic techniques, the available functions with which to construct such formulas, and creativity to apply them. So you should read up on both, and ask how to implement such ideas, not just on google, but via experimentation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.