Filter help needed

[Guest]

Hi, Question example: In cell A1 is the value of 50. I click on A1 to Autofilter. When sorting the column using the "custom Autofilter" window, what code do I put in the upper right and lower right fields if I want to find data for example that is a value of 10 higher and a value of 10 lower than the 50 in A1? Also, what goes in the left upper and lower fields
Then I want to select B1 with a value of 100 and do the same thing of 10 above and 10 below. Is it possible to sort like this
I appreciate anyone who can help!

 

[JulieD]

Hi

you've lost me .. and guessing by the fact you've not had any responses to
your ealier post in excel.misc you've lost other people too ...

but as i would like to help, maybe you can provide additional information
1) what version of excel are you using (help / about on the toolbar)
2) did you create this workbook or did someone else?
3) how to you "autofilter" ... what menu options / steps?
4) how do you display the "custom autofilter" window - what menu options /
steps
5) what is the label next to the left upper & lower fields?

Awaiting your reply
Cheers
JulieD

Hi, Question example: In cell A1 is the value of 50. I click on A1 to

Autofilter. When sorting the column using the "custom Autofilter" window,
what code do I put in the upper right and lower right fields if I want to
find data for example that is a value of 10 higher and a value of 10 lower
than the 50 in A1? Also, what goes in the left upper and lower fields?

Then I want to select B1 with a value of 100 and do the same thing of 10

above and 10 below. Is it possible to sort like this?

 

[Guest]

Hello Julie D, Thank for your help. I am using an older Excel version 97. I created this workspace. My "A" column contains few hundred different numbers down it. I click on cell A1, which the value is 50. On the tool bar at top I click on 'Data' then 'Filter' then 'Autofilter'. This places the down arrow button in cell A1. I click the down arrow button, then 'custom'. This opens up a custom autofilter window which asks you basicly which rows you want to show relative to the "50" value. I'm not sure how it will look in this 'discussion window' but looks something like this

Show rows where
5
is greater than <---(upper left field) (upper right field)---> code I need goes here
and o
is less than <---(lower left field) (lower right field)---> code I need goes her

It should look like that. The greater than or less than options also include- equals, contains, begins with, ends with, etc
The reason I accidently posted my original twice was I couldn't find it for a while after I posted it, didn't know it existed. Hope this shows up o.k. in the window for you
I do appreciate your help Jeff

 

[Guest]

Hi Jeff,

Am I right on saying that you want to filter out values
between 40 and 60 (ie 10 above and 10 below)? pls explain
and i will try and help you out...

Danny

-----Original Message-----
Hi, Question example: In cell A1 is the value of 50. I

click on A1 to Autofilter. When sorting the column using
the "custom Autofilter" window, what code do I put in the
upper right and lower right fields if I want to find data
for example that is a value of 10 higher and a value of 10
lower than the 50 in A1? Also, what goes in the left
upper and lower fields?

Then I want to select B1 with a value of 100 and do the

same thing of 10 above and 10 below. Is it possible to
sort like this?

 

[JulieD]

Hi jeff

this makes way more sense - you threw me in the first one by using the word
"sort"

AFAIK it can't be done in an autofilter (but i'm waiting for someone to
prove me wrong) - i think, however you can achieve what you want by using an
Advanced Filter

to use advanced filter

insert a couple of rows above your data - its a good idea (and, in this
case, i think, necessary) to have names above the data you wish to filter so
assuming your worksheet now looks like this:

A B C
1
2
3
4
5 Figures1 Figures2 More Figures
6 50 xx xxx
7 xx xxx xx

copy the heading in A6 (Figures 1) into cell A1
then click in A2 type
=A6+10
the click in A3
and type
=A6-10

then click in A6
from the menu choose Data / Filter / Advanced filter
your list range should already be filled in, click in the criteria range box
and
then highlight A1:A3 on your worksheet

now press OK and you should have what i think you're after - to see all your
records again choose Data / Filter / Show All

Let us know how you go.

Cheers
JulieD

Hello Julie D, Thank for your help. I am using an older Excel version

97. I created this workspace. My "A" column contains few hundred different
numbers down it. I click on cell A1, which the value is 50. On the tool bar
at top I click on 'Data' then 'Filter' then 'Autofilter'. This places the
down arrow button in cell A1. I click the down arrow button, then 'custom'.
This opens up a custom autofilter window which asks you basicly which rows
you want to show relative to the "50" value. I'm not sure how it will look
in this 'discussion window' but looks something like this:

Show rows where:
50
is greater than <---(upper left field) (upper right field)---> code I need goes here
and or
is less than <---(lower left field) (lower right field)---> code I need goes here

It should look like that. The greater than or less than options also

include- equals, contains, begins with, ends with, etc.

The reason I accidently posted my original twice was I couldn't find it

for a while after I posted it, didn't know it existed. Hope this shows up
o.k. in the window for you.

 

[JulieD]

Hi Terry

i've got a sample workbook doing what i outlined in my last post that also
has a bit of a "twist" to it to easily allow you to change which columns you
want to filter on ... if you'ld like a copy of it email me direct & i'll
send it.

Cheers
JulieD

Hello Julie D, Thank for your help. I am using an older Excel version

97. I created this workspace. My "A" column contains few hundred different
numbers down it. I click on cell A1, which the value is 50. On the tool bar
at top I click on 'Data' then 'Filter' then 'Autofilter'. This places the
down arrow button in cell A1. I click the down arrow button, then 'custom'.
This opens up a custom autofilter window which asks you basicly which rows
you want to show relative to the "50" value. I'm not sure how it will look
in this 'discussion window' but looks something like this:

Show rows where:
50
is greater than <---(upper left field) (upper right field)---> code I need goes here
and or
is less than <---(lower left field) (lower right field)---> code I need goes here

It should look like that. The greater than or less than options also

include- equals, contains, begins with, ends with, etc.

The reason I accidently posted my original twice was I couldn't find it

for a while after I posted it, didn't know it existed. Hope this shows up
o.k. in the window for you.

 

[Guest]

Hi JulieD, Thank you so much for the last tip on the advanced filter. You are very helpful. I did enter as you suggested and we are getting very close. For some reason after running the filter, it shows my 50 value in cell 1A and one other value of 60 in cell 2A. But I know there are alot more values that should have resulted from this filter process. Does the + or -characters only find something 10 higher or lower instead of what I prefer where it shows all values within the range between 40 & 60 values
As you commented about e-mailing me a copy of a sample notebook, I would be open to that but how do I get you direct e-mail? Sorry for the elementary questions that are challenging for me but a snap for others in this arena. Again, thank you for your patience
Jeff

 

[Guest]

Hi Danny, JulieD has been helping me and we are not quite there yet. I don't want to waist your time but maybe you can toss and idea out there for me if you wish. If not I understand. The following is an new explanation of my original post since it wasn't clearly explained
I am using an older Excel version 97. I created this workspace. My "A" column contains few hundred different numbers down it. I click on cell A1, which the value is 50. On the tool bar at top I click on 'Data' then 'Filter' then 'Autofilter'. This places the down arrow button in cell A1. I click the down arrow button, then 'custom'. This opens up a custom autofilter window which asks you basicly which rows you want to show relative to the "50" value. I'm not sure how it will look in this 'discussion window' but looks something like this

Show rows where
5
is greater than <---(upper left field) (upper right field)---> code I need goes here
and o
is less than <---(lower left field) (lower right field)---> code I need goes her

It should look like that. The greater than or less than options also include- equals, contains, begins with, ends with, etc
The reason I accidently posted my original twice was I couldn't find it for a while after I posted it, didn't know it existed. Hope this shows up o.k. in the window for you
I do appreciate your help Jeff

 

[Guest]

Hi JulieD, In my last reply to you, I made an error.
I said: it shows my 50 value in cell 1A and one other value of 60 in cell 2A.
It should have said:it shows my 50 value in cell A6 and one other value of 60 in cell A7.
Sorry for the mistake,
Jeff

 

[JulieD]

Hi Jeff

sorry didn't realise you wanted a range returned ... i can't actually figure
out how to do it ...

this works ... but it means you have to type the value of A1 into the
criteria rather than it picking it up itself
A B
1 leave blank
2 =AND(A6>=50,A6<=150)
3
4 Fvalues
5 100
6 10
7 120
8 50

leave A1 blank, select A1 & A2 as the criteria range for the advance filter
and filter just like you did in the previous post - however, as i said .. i
can't get a formula in where it has >=50 (e.g. >=A6-50) and <=150 (e.g.
<=A6+50) to work. Maybe someone else can spread some light on this.

Cheers
JulieD

Hi JulieD, Thank you so much for the last tip on the advanced filter.

You are very helpful. I did enter as you suggested and we are getting very
close. For some reason after running the filter, it shows my 50 value in
cell 1A and one other value of 60 in cell 2A. But I know there are alot
more values that should have resulted from this filter process. Does the +
or -characters only find something 10 higher or lower instead of what I
prefer where it shows all values within the range between 40 & 60 values?

As you commented about e-mailing me a copy of a sample notebook, I would

be open to that but how do I get you direct e-mail? Sorry for the
elementary questions that are challenging for me but a snap for others in
this arena. Again, thank you for your patience!