Sorry if this posted twice - I received an error when trying to submit...

Hi,

Sorry about the weird subject, I wasn't sure how to word this one. Here is
some data to use as a guide:

STATEFP ANPSADPI TOTPOP
1 Anniston city 26623
1 Bessemer city 33497
1 Birmingham city 200000
1 Decatur city 48796
1 Dothan city 53583
1 Florence city 36521
1 Gadsden city 42523
1 Huntsville city 159866
1 Mobile city 196278

This is just a small selection from the data - STATEFP is column A /
ANPSADPI is column B / TOTPOP is column C.

My user would like to find a city - Birmingham City for example - and find
the related population for that city. Then, return a list of cities and their
populations that fall within 100,000 +/- of the this city (Birmingham).

If Birmingham's population is 200,000, list any cities where their
population falls between 100,000 and 300,000.

The end result should display the list of cities and their populations.

I feel like I can do this by using an advanced filter, but am not sure how
to have excel look for the +/- data within the populations. Or is there a
function that could work this out? Please help my brain with this one! It's
starting to hurt!

Thanks for any suggestions!

Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down...

=IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0)

Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1.

--
Rick (MVP - Excel)


"Tammy" wrote in message ...
Sorry if this posted twice - I received an error when trying to submit...

Hi,

Sorry about the weird subject, I wasn't sure how to word this one. Here is
some data to use as a guide:

STATEFP ANPSADPI TOTPOP
1 Anniston city 26623
1 Bessemer city 33497
1 Birmingham city 200000
1 Decatur city 48796
1 Dothan city 53583
1 Florence city 36521
1 Gadsden city 42523
1 Huntsville city 159866
1 Mobile city 196278

This is just a small selection from the data - STATEFP is column A /
ANPSADPI is column B / TOTPOP is column C.

My user would like to find a city - Birmingham City for example - and find
the related population for that city. Then, return a list of cities and their
populations that fall within 100,000 +/- of the this city (Birmingham).

If Birmingham's population is 200,000, list any cities where their
population falls between 100,000 and 300,000.

The end result should display the list of cities and their populations.

I feel like I can do this by using an advanced filter, but am not sure how
to have excel look for the +/- data within the populations. Or is there a
function that could work this out? Please help my brain with this one! It's
starting to hurt!

Thanks for any suggestions!

Hi Rick,

Thanks so much for your response!

This will be great, if I can get it to work. I keep coming up with all
zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data.

Here is what i have entered:
=IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0)

**I assumed your "C3" in the formula represented where "Birmingham city" was
located in my example below. I changed C3 to the actual cell that contained
that city name - I also changed it to an absolute value because the formula
changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute.
If that was wrong, and C3 should be a relative reference, or actually
represents something else, please let me know.

**I changed the lookup range to the actual data range.

**I changed the "less than" value to 500,000 (I used 100,000 yesterday to
keep things related to the data I submitted.)

Assuming Birmingham's population is 1,500,000, the lookup should return a
true value of "1" if another city's population falls between 1,000,000 and
2,000,000.
(+/- 500,000 of Birmingham's population)

When I copy this down, I get all zeros - there are some cities that fall
within the 1-2 million range.

The formula looks like it looks up the value in D2 in the range B2:B526. The
population in C2:C526 is used for the "less than" value(?). One thing I can't
figure out from by looking at the formula, where does it take into account
the value of Birmingham's population and search for the +/- 500,000? Should
there be another nested IF function to represent the "greater than" side?

This is such a great start! I appreciate you taking the time to answer. Can
you help me a little further with this one?

Thanks!

"Rick Rothstein" wrote:

Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down...

=IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0)

Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1.

--
Rick (MVP - Excel)


"Tammy" wrote in message ...
Sorry if this posted twice - I received an error when trying to submit...

Hi,

Sorry about the weird subject, I wasn't sure how to word this one. Here is
some data to use as a guide:

STATEFP ANPSADPI TOTPOP
1 Anniston city 26623
1 Bessemer city 33497
1 Birmingham city 200000
1 Decatur city 48796
1 Dothan city 53583
1 Florence city 36521
1 Gadsden city 42523
1 Huntsville city 159866
1 Mobile city 196278

This is just a small selection from the data - STATEFP is column A /
ANPSADPI is column B / TOTPOP is column C.

My user would like to find a city - Birmingham City for example - and find
the related population for that city. Then, return a list of cities and their
populations that fall within 100,000 +/- of the this city (Birmingham).

If Birmingham's population is 200,000, list any cities where their
population falls between 100,000 and 300,000.

The end result should display the list of cities and their populations.

I feel like I can do this by using an advanced filter, but am not sure how
to have excel look for the +/- data within the populations. Or is there a
function that could work this out? Please help my brain with this one! It's
starting to hurt!

Thanks for any suggestions!

Sorry - "C399" represents the population, not the city name.

"Tammy" wrote:

Hi Rick,

Thanks so much for your response!

This will be great, if I can get it to work. I keep coming up with all
zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data.

Here is what i have entered:
=IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0)

**I assumed your "C3" in the formula represented where "Birmingham city" was
located in my example below. I changed C3 to the actual cell that contained
that city name - I also changed it to an absolute value because the formula
changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute.
If that was wrong, and C3 should be a relative reference, or actually
represents something else, please let me know.

**I changed the lookup range to the actual data range.

**I changed the "less than" value to 500,000 (I used 100,000 yesterday to
keep things related to the data I submitted.)

Assuming Birmingham's population is 1,500,000, the lookup should return a
true value of "1" if another city's population falls between 1,000,000 and
2,000,000.
(+/- 500,000 of Birmingham's population)

When I copy this down, I get all zeros - there are some cities that fall
within the 1-2 million range.

The formula looks like it looks up the value in D2 in the range B2:B526. The
population in C2:C526 is used for the "less than" value(?). One thing I can't
figure out from by looking at the formula, where does it take into account
the value of Birmingham's population and search for the +/- 500,000? Should
there be another nested IF function to represent the "greater than" side?

This is such a great start! I appreciate you taking the time to answer. Can
you help me a little further with this one?

Thanks!

"Rick Rothstein" wrote:

Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down...

=IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0)

Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1.

--
Rick (MVP - Excel)


"Tammy" wrote in message ...
Sorry if this posted twice - I received an error when trying to submit...

Hi,

Sorry about the weird subject, I wasn't sure how to word this one. Here is
some data to use as a guide:

STATEFP ANPSADPI TOTPOP
1 Anniston city 26623
1 Bessemer city 33497
1 Birmingham city 200000
1 Decatur city 48796
1 Dothan city 53583
1 Florence city 36521
1 Gadsden city 42523
1 Huntsville city 159866
1 Mobile city 196278

This is just a small selection from the data - STATEFP is column A /
ANPSADPI is column B / TOTPOP is column C.

My user would like to find a city - Birmingham City for example - and find
the related population for that city. Then, return a list of cities and their
populations that fall within 100,000 +/- of the this city (Birmingham).

If Birmingham's population is 200,000, list any cities where their
population falls between 100,000 and 300,000.

The end result should display the list of cities and their populations.

I feel like I can do this by using an advanced filter, but am not sure how
to have excel look for the +/- data within the populations. Or is there a
function that could work this out? Please help my brain with this one! It's
starting to hurt!

Thanks for any suggestions!

The C3 was supposed to have been C2 (with no $ signs). Using your latest
post, put this formula on Row 2 somewhere and copy it down...

=IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0)

--
Rick (MVP - Excel)


"Tammy" wrote in message
...
Hi Rick,

Thanks so much for your response!

This will be great, if I can get it to work. I keep coming up with all
zeros, so i'm doing something wrong. I did "tweak" the formula to fit my
data.

Here is what i have entered:
=IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0)

**I assumed your "C3" in the formula represented where "Birmingham city"
was
located in my example below. I changed C3 to the actual cell that
contained
that city name - I also changed it to an absolute value because the
formula
changed as it was copied down (c3, c4, c5, etc.) if I didn't use an
absolute.
If that was wrong, and C3 should be a relative reference, or actually
represents something else, please let me know.

**I changed the lookup range to the actual data range.

**I changed the "less than" value to 500,000 (I used 100,000 yesterday to
keep things related to the data I submitted.)

Assuming Birmingham's population is 1,500,000, the lookup should return a
true value of "1" if another city's population falls between 1,000,000 and
2,000,000.
(+/- 500,000 of Birmingham's population)

When I copy this down, I get all zeros - there are some cities that fall
within the 1-2 million range.

The formula looks like it looks up the value in D2 in the range B2:B526.
The
population in C2:C526 is used for the "less than" value(?). One thing I
can't
figure out from by looking at the formula, where does it take into account
the value of Birmingham's population and search for the +/- 500,000?
Should
there be another nested IF function to represent the "greater than" side?

This is such a great start! I appreciate you taking the time to answer.
Can
you help me a little further with this one?

Thanks!

"Rick Rothstein" wrote:

Assuming you have header (so your data starts in Row 2) and assuming the
search city's name is in D2, put this formula in a blank column starting
on Row 2 and copy it down...

=IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0)

Change the ranges row of 1000 to a number representing the maximum row
number you could have data in. You can now Auto Filter on the 1.

--
Rick (MVP - Excel)


"Tammy" wrote in message
...
Sorry if this posted twice - I received an error when trying to
submit...

Hi,

Sorry about the weird subject, I wasn't sure how to word this one. Here
is
some data to use as a guide:

STATEFP ANPSADPI TOTPOP
1 Anniston city 26623
1 Bessemer city 33497
1 Birmingham city 200000
1 Decatur city 48796
1 Dothan city 53583
1 Florence city 36521
1 Gadsden city 42523
1 Huntsville city 159866
1 Mobile city 196278

This is just a small selection from the data - STATEFP is column A /
ANPSADPI is column B / TOTPOP is column C.

My user would like to find a city - Birmingham City for example - and
find
the related population for that city. Then, return a list of cities and
their
populations that fall within 100,000 +/- of the this city
(Birmingham).

If Birmingham's population is 200,000, list any cities where their
population falls between 100,000 and 300,000.

The end result should display the list of cities and their populations.

I feel like I can do this by using an advanced filter, but am not sure
how
to have excel look for the +/- data within the populations. Or is there
a
function that could work this out? Please help my brain with this one!
It's
starting to hurt!

Thanks for any suggestions!

Hi Rick,

Thanks, again, for your help!

I pasted and copied your formula as is (no tweaking this time), however am
still getting inaccurate results. (so sorry about this).

The cities and populations in my results for "1" have populations between
12,818 - 516,259
and the results for the "0" populations are between 574,283 - 7,322,564

The results, after filtering for "1", should display only cities with a
population greater than or equal to 1 million and less than or equal to 2
million.
(between 1 and 2 million:
1,500,000 (Birmingham population) - 500,000 = 1,000,000
AND
1,500,000 (Birmingham population) + 500,000 = 2,000,000).

Any other suggestions? Thanks so much!

"Rick Rothstein" wrote:

The C3 was supposed to have been C2 (with no $ signs). Using your latest
post, put this formula on Row 2 somewhere and copy it down...

=IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0)

--
Rick (MVP - Excel)


"Tammy" wrote in message
...
Hi Rick,

Thanks so much for your response!

This will be great, if I can get it to work. I keep coming up with all
zeros, so i'm doing something wrong. I did "tweak" the formula to fit my
data.

Here is what i have entered:
=IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0)

**I assumed your "C3" in the formula represented where "Birmingham city"
was
located in my example below. I changed C3 to the actual cell that
contained
that city name - I also changed it to an absolute value because the
formula
changed as it was copied down (c3, c4, c5, etc.) if I didn't use an
absolute.
If that was wrong, and C3 should be a relative reference, or actually
represents something else, please let me know.

**I changed the lookup range to the actual data range.

**I changed the "less than" value to 500,000 (I used 100,000 yesterday to
keep things related to the data I submitted.)

Assuming Birmingham's population is 1,500,000, the lookup should return a
true value of "1" if another city's population falls between 1,000,000 and
2,000,000.
(+/- 500,000 of Birmingham's population)

When I copy this down, I get all zeros - there are some cities that fall
within the 1-2 million range.

The formula looks like it looks up the value in D2 in the range B2:B526.
The
population in C2:C526 is used for the "less than" value(?). One thing I
can't
figure out from by looking at the formula, where does it take into account
the value of Birmingham's population and search for the +/- 500,000?
Should
there be another nested IF function to represent the "greater than" side?

This is such a great start! I appreciate you taking the time to answer.
Can
you help me a little further with this one?

Thanks!

"Rick Rothstein" wrote:

Assuming you have header (so your data starts in Row 2) and assuming the
search city's name is in D2, put this formula in a blank column starting
on Row 2 and copy it down...

=IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0)

Change the ranges row of 1000 to a number representing the maximum row
number you could have data in. You can now Auto Filter on the 1.

--
Rick (MVP - Excel)


"Tammy" wrote in message
...
Sorry if this posted twice - I received an error when trying to
submit...

Hi,

Sorry about the weird subject, I wasn't sure how to word this one. Here
is
some data to use as a guide:

STATEFP ANPSADPI TOTPOP
1 Anniston city 26623
1 Bessemer city 33497
1 Birmingham city 200000
1 Decatur city 48796
1 Dothan city 53583
1 Florence city 36521
1 Gadsden city 42523
1 Huntsville city 159866
1 Mobile city 196278

This is just a small selection from the data - STATEFP is column A /
ANPSADPI is column B / TOTPOP is column C.

My user would like to find a city - Birmingham City for example - and
find
the related population for that city. Then, return a list of cities and
their
populations that fall within 100,000 +/- of the this city
(Birmingham).

If Birmingham's population is 200,000, list any cities where their
population falls between 100,000 and 300,000.

The end result should display the list of cities and their populations.

I feel like I can do this by using an advanced filter, but am not sure
how
to have excel look for the +/- data within the populations. Or is there
a
function that could work this out? Please help my brain with this one!
It's
starting to hurt!

Thanks for any suggestions!

Try the formula again, but this time change the 526 to the exact last row
that data there is a city name in (I mistakenly told you that the last row
in the range could be any number greater than the last row with data...
LOOKUP appears to be more sensitive than that). IF this is what you already
have done, then try this formula instead...

=IF(ABS(C2-LOOKUP(D$2&"*",B$2:B$526,C$2:C$526))<=500000,1,0)

It will ignore an trailing spaces that you may have attached to your city
names. If that doesn't work, then post some *real* example data from your
worksheet (I notice that your original sample population data seems to be in
a different range than what you originally posted... possibly the problem
might be we are working with different data).

--
Rick (MVP - Excel)


"Tammy" wrote in message
...
Hi Rick,

Thanks, again, for your help!

I pasted and copied your formula as is (no tweaking this time), however am
still getting inaccurate results. (so sorry about this).

The cities and populations in my results for "1" have populations between
12,818 - 516,259
and the results for the "0" populations are between 574,283 - 7,322,564

The results, after filtering for "1", should display only cities with a
population greater than or equal to 1 million and less than or equal to 2
million.
(between 1 and 2 million:
1,500,000 (Birmingham population) - 500,000 = 1,000,000
AND
1,500,000 (Birmingham population) + 500,000 = 2,000,000).

Any other suggestions? Thanks so much!

"Rick Rothstein" wrote:

The C3 was supposed to have been C2 (with no $ signs). Using your latest
post, put this formula on Row 2 somewhere and copy it down...

=IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0)

--
Rick (MVP - Excel)


"Tammy" wrote in message
...
Hi Rick,

Thanks so much for your response!

This will be great, if I can get it to work. I keep coming up with all
zeros, so i'm doing something wrong. I did "tweak" the formula to fit
my
data.

Here is what i have entered:
=IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0)

**I assumed your "C3" in the formula represented where "Birmingham
city"
was
located in my example below. I changed C3 to the actual cell that
contained
that city name - I also changed it to an absolute value because the
formula
changed as it was copied down (c3, c4, c5, etc.) if I didn't use an
absolute.
If that was wrong, and C3 should be a relative reference, or actually
represents something else, please let me know.

**I changed the lookup range to the actual data range.

**I changed the "less than" value to 500,000 (I used 100,000 yesterday
to
keep things related to the data I submitted.)

Assuming Birmingham's population is 1,500,000, the lookup should return
a
true value of "1" if another city's population falls between 1,000,000
and
2,000,000.
(+/- 500,000 of Birmingham's population)

When I copy this down, I get all zeros - there are some cities that
fall
within the 1-2 million range.

The formula looks like it looks up the value in D2 in the range
B2:B526.
The
population in C2:C526 is used for the "less than" value(?). One thing I
can't
figure out from by looking at the formula, where does it take into
account
the value of Birmingham's population and search for the +/- 500,000?
Should
there be another nested IF function to represent the "greater than"
side?

This is such a great start! I appreciate you taking the time to answer.
Can
you help me a little further with this one?

Thanks!

"Rick Rothstein" wrote:

Assuming you have header (so your data starts in Row 2) and assuming
the
search city's name is in D2, put this formula in a blank column
starting
on Row 2 and copy it down...

=IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0)

Change the ranges row of 1000 to a number representing the maximum row
number you could have data in. You can now Auto Filter on the 1.

--
Rick (MVP - Excel)


"Tammy" wrote in message
...
Sorry if this posted twice - I received an error when trying to
submit...

Hi,

Sorry about the weird subject, I wasn't sure how to word this one.
Here
is
some data to use as a guide:

STATEFP ANPSADPI TOTPOP
1 Anniston city 26623
1 Bessemer city 33497
1 Birmingham city 200000
1 Decatur city 48796
1 Dothan city 53583
1 Florence city 36521
1 Gadsden city 42523
1 Huntsville city 159866
1 Mobile city 196278

This is just a small selection from the data - STATEFP is column A /
ANPSADPI is column B / TOTPOP is column C.

My user would like to find a city - Birmingham City for example -
and
find
the related population for that city. Then, return a list of cities
and
their
populations that fall within 100,000 +/- of the this city
(Birmingham).

If Birmingham's population is 200,000, list any cities where their
population falls between 100,000 and 300,000.

The end result should display the list of cities and their
populations.

I feel like I can do this by using an advanced filter, but am not
sure
how
to have excel look for the +/- data within the populations. Or is
there
a
function that could work this out? Please help my brain with this
one!
It's
starting to hurt!

Thanks for any suggestions!