Hello all,

I'm having a bit of a problem that I hope that someone can help me
with. Here's my sample data range:
City, Rate
Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

I want to populate a two-column, five row table with the city and rate.
I've used the SMALL function in the rate column to include the five
lowest rates. My problem is, I'm able to retrieve the rates, but I
can't populate the first column with the City.

What I need is if column B=14, then A=Baltimore and such. Does anyone
have any ideas how I can set that up?

Holli - who hopes that was semi-coherent

Use this to get the cities in order of smallest first

=INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0))

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"HBuck" wrote in message
ups.com...
Hello all,

I'm having a bit of a problem that I hope that someone can help me
with. Here's my sample data range:
City, Rate
Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

I want to populate a two-column, five row table with the city and rate.
I've used the SMALL function in the rate column to include the five
lowest rates. My problem is, I'm able to retrieve the rates, but I
can't populate the first column with the City.

What I need is if column B=14, then A=Baltimore and such. Does anyone
have any ideas how I can set that up?

Holli - who hopes that was semi-coherent

Thanks for that, Bob!

Can you please tell me how I would do that for multiple columns? I
thought I had a handle on it, but I received an error message that I
can't seem to get myself out of.

Thanks,
Holli


Bob Phillips wrote:
Use this to get the cities in order of smallest first

=INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0))

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"HBuck" wrote in message
ups.com...
Hello all,

I'm having a bit of a problem that I hope that someone can help me
with. Here's my sample data range:
City, Rate
Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

I want to populate a two-column, five row table with the city and rate.
I've used the SMALL function in the rate column to include the five
lowest rates. My problem is, I'm able to retrieve the rates, but I
can't populate the first column with the City.

What I need is if column B=14, then A=Baltimore and such. Does anyone
have any ideas how I can set that up?

Holli - who hopes that was semi-coherent

What does the data look like?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"HBuck" wrote in message
oups.com...
Thanks for that, Bob!

Can you please tell me how I would do that for multiple columns? I
thought I had a handle on it, but I received an error message that I
can't seem to get myself out of.

Thanks,
Holli


Bob Phillips wrote:
Use this to get the cities in order of smallest first

=INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0))

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"HBuck" wrote in message
ups.com...
Hello all,

I'm having a bit of a problem that I hope that someone can help me
with. Here's my sample data range:
City, Rate
Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

I want to populate a two-column, five row table with the city and
rate.
I've used the SMALL function in the rate column to include the five
lowest rates. My problem is, I'm able to retrieve the rates, but I
can't populate the first column with the City.

What I need is if column B=14, then A=Baltimore and such. Does anyone
have any ideas how I can set that up?

Holli - who hopes that was semi-coherent

Thanks for responding, Bob.

The data looks like the following:

Cells B6 to C12

C-Sites Score
Detroit 25
Chicago 50
Baltimore 14
Atlanta 23
San Francisco 42
San Jose 39

Cells E6 to F9

D-Sites Score
Palo Alto 12
Canton 39
Omaha 14

I need to pull the city data from B7:B12, E7:E9 and the scores from
C7:C12, F7:F9.

There can definitely be duplicates in the scores.

Hope that this helps. Thanks!

Holli







Bob Phillips wrote:
What does the data look like?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"HBuck" wrote in message
oups.com...
Thanks for that, Bob!

Can you please tell me how I would do that for multiple columns? I
thought I had a handle on it, but I received an error message that I
can't seem to get myself out of.

Thanks,
Holli


Bob Phillips wrote:
Use this to get the cities in order of smallest first

=INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0))

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"HBuck" wrote in message
ups.com...
Hello all,

I'm having a bit of a problem that I hope that someone can help me
with. Here's my sample data range:
City, Rate
Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

I want to populate a two-column, five row table with the city and
rate.
I've used the SMALL function in the rate column to include the five
lowest rates. My problem is, I'm able to retrieve the rates, but I
can't populate the first column with the City.

What I need is if column B=14, then A=Baltimore and such. Does anyone
have any ideas how I can set that up?

Holli - who hopes that was semi-coherent

Holli,

does the data need to be in two separate tables? Can you combine it
into one table using another column for Site-type, like the following?

Type Site Score
C Detroit 25
C Chicago 50
C Baltimore 14
C Atlanta 23
C San Francisco 42
C San Jose 39
D Palo Alto 12
D Canton 39
D Omaha 14

This would make it a bit easier to do what you are asking.

Hope this helps.

Pete

HBuck wrote:
Thanks for responding, Bob.

The data looks like the following:

Cells B6 to C12

C-Sites Score
Detroit 25
Chicago 50
Baltimore 14
Atlanta 23
San Francisco 42
San Jose 39

Cells E6 to F9

D-Sites Score
Palo Alto 12
Canton 39
Omaha 14

I need to pull the city data from B7:B12, E7:E9 and the scores from
C7:C12, F7:F9.

There can definitely be duplicates in the scores.

Hope that this helps. Thanks!

Holli







Bob Phillips wrote:
What does the data look like?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"HBuck" wrote in message
oups.com...
Thanks for that, Bob!

Can you please tell me how I would do that for multiple columns? I
thought I had a handle on it, but I received an error message that I
can't seem to get myself out of.

Thanks,
Holli


Bob Phillips wrote:
Use this to get the cities in order of smallest first

=INDEX($A$1:$A$100,MATCH(SMALL($B$1:$B$100,ROW(A1) ),$B$1:$B$100,0))

and copy down

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"HBuck" wrote in message
ups.com...
Hello all,

I'm having a bit of a problem that I hope that someone can help me
with. Here's my sample data range:
City, Rate
Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

I want to populate a two-column, five row table with the city and
rate.
I've used the SMALL function in the rate column to include the five
lowest rates. My problem is, I'm able to retrieve the rates, but I
can't populate the first column with the City.

What I need is if column B=14, then A=Baltimore and such. Does anyone
have any ideas how I can set that up?

Holli - who hopes that was semi-coherent

Try this:

This table is in the range A2:B7

Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

Enter this formula in D2:

=INDEX(A$2:A$7,MATCH(E2,B$2:B$7,0))

Enter this formula in E2:

=SMALL(B$2:B$7,ROWS($1:1))

Select both D2 and E2 and copy down 5 rows.

Based on your sample data there are no duplicate numbers. If there are this
will be more complicated. Post back if that's the case.

Biff

"HBuck" wrote in message
ups.com...
Hello all,

I'm having a bit of a problem that I hope that someone can help me
with. Here's my sample data range:
City, Rate
Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

I want to populate a two-column, five row table with the city and rate.
I've used the SMALL function in the rate column to include the five
lowest rates. My problem is, I'm able to retrieve the rates, but I
can't populate the first column with the City.

What I need is if column B=14, then A=Baltimore and such. Does anyone
have any ideas how I can set that up?

Holli - who hopes that was semi-coherent

Biff,

Going through the numbers, not only are there a few duplicates, but the
data is actually in more than one column. It just seems to get messier
as we go along. Can you please tell me how to work around that?

Thanks!
Holli



Biff wrote:
Try this:

This table is in the range A2:B7

Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

Enter this formula in D2:

=INDEX(A$2:A$7,MATCH(E2,B$2:B$7,0))

Enter this formula in E2:

=SMALL(B$2:B$7,ROWS($1:1))

Select both D2 and E2 and copy down 5 rows.

Based on your sample data there are no duplicate numbers. If there are this
will be more complicated. Post back if that's the case.

Biff

"HBuck" wrote in message
ups.com...
Hello all,

I'm having a bit of a problem that I hope that someone can help me
with. Here's my sample data range:
City, Rate
Chicago, 50
New York, 23
San Francisco, 43
San Jose, 55
Baltimore, 14
Ann Arbor, 28

I want to populate a two-column, five row table with the city and rate.
I've used the SMALL function in the rate column to include the five
lowest rates. My problem is, I'm able to retrieve the rates, but I
can't populate the first column with the City.

What I need is if column B=14, then A=Baltimore and such. Does anyone
have any ideas how I can set that up?

Holli - who hopes that was semi-coherent