I have windows XP 2003 home edition.
I have a name and number in cell a1; john---22, I have a name in number
in b1; James---27, I have just a number in cell c1; "3" (ignore
quotes). In cell d1 I would like to show the name only (from cell a1 or
b1) that has the lowest number along with the number in cell c1. The
end result would look like this; John +3.
Here a sample of what my work sheet looks like and the result I am
seeking.
a1 b1 c1 d1
John---22 James---27 3 john +3
a2 b2 c2 d2
Joe---90 Betty---60 4 Betty +12
etc, etc down through a8:d8.
If a zero appears in column "c", then no name appears in column "d"
just a zero without the plus sign.
The range I am working with is a1:d8.
Thanks for any help you can provide.
WallyB

Betty +12

Shouldn't that be: Betty +4 ?

Also, what if the the values are the same:

John---22 James---22

Are the numbers always 2 digits:

John---22
James---27
Joe---90
Betty---60

This seems familiar to me. Did I reply to a post of yours about a month or
so ago?

Biff

"wally" wrote in message
oups.com...
I have windows XP 2003 home edition.
I have a name and number in cell a1; john---22, I have a name in number
in b1; James---27, I have just a number in cell c1; "3" (ignore
quotes). In cell d1 I would like to show the name only (from cell a1 or
b1) that has the lowest number along with the number in cell c1. The
end result would look like this; John +3.
Here a sample of what my work sheet looks like and the result I am
seeking.
a1 b1 c1 d1
John---22 James---27 3 john +3
a2 b2 c2 d2
Joe---90 Betty---60 4 Betty +12
etc, etc down through a8:d8.
If a zero appears in column "c", then no name appears in column "d"
just a zero without the plus sign.
The range I am working with is a1:d8.
Thanks for any help you can provide.
WallyB

Hi Biff, you did reply to my post about a month ago. I have used what
you sent me to great sucess, now I would like to refine it to one more
step. Yes, it should be Betty +4 and it will always 2 digits. The name
is followed by 3 dashes. If the values are the same, then the number
will show a zero in the "c" column. The "d" column will then have just
a plain zero, no name or + sign.
Example: John---22 James---22 0 0
Thanks, Wally

Biff wrote:
Betty +12

Shouldn't that be: Betty +4 ?

Also, what if the the values are the same:

John---22 James---22

Are the numbers always 2 digits:

John---22
James---27
Joe---90
Betty---60

This seems familiar to me. Did I reply to a post of yours about a month or
so ago?

Biff

"wally" wrote in message
oups.com...
I have windows XP 2003 home edition.
I have a name and number in cell a1; john---22, I have a name in number
in b1; James---27, I have just a number in cell c1; "3" (ignore
quotes). In cell d1 I would like to show the name only (from cell a1 or
b1) that has the lowest number along with the number in cell c1. The
end result would look like this; John +3.
Here a sample of what my work sheet looks like and the result I am
seeking.
a1 b1 c1 d1
John---22 James---27 3 john +3
a2 b2 c2 d2
Joe---90 Betty---60 4 Betty +12
etc, etc down through a8:d8.
If a zero appears in column "c", then no name appears in column "d"
just a zero without the plus sign.
The range I am working with is a1:d8.
Thanks for any help you can provide.
WallyB

One way (until Biff comes back with something much smaller!):

=LEFT(IF((REPLACE(A1,1,SEARCH("---",A1)+2,""))
(REPLACE(B1,1,SEARCH("---",B1)+2,"")),A1,B1),
SEARCH("---",IF((REPLACE(A1,1,SEARCH("---",A1)+2,""))
(REPLACE(B1,1,SEARCH("---",B1)+2,"")),A1,B1))-1)&" +"&C1

(That's all one cell--paste in the formula bar with D1 selected)

wally wrote:

I have windows XP 2003 home edition.
I have a name and number in cell a1; john---22, I have a name in number
in b1; James---27, I have just a number in cell c1; "3" (ignore
quotes). In cell d1 I would like to show the name only (from cell a1 or
b1) that has the lowest number along with the number in cell c1. The
end result would look like this; John +3.
Here a sample of what my work sheet looks like and the result I am
seeking.
a1 b1 c1 d1
John---22 James---27 3 john +3
a2 b2 c2 d2
Joe---90 Betty---60 4 Betty +12
etc, etc down through a8:d8.
If a zero appears in column "c", then no name appears in column "d"
just a zero without the plus sign.
The range I am working with is a1:d8.
Thanks for any help you can provide.
WallyB

--

Dave Peterson

=if(c1=0,0, thatlongformula)


Dave Peterson wrote:

One way (until Biff comes back with something much smaller!):

=LEFT(IF((REPLACE(A1,1,SEARCH("---",A1)+2,""))
(REPLACE(B1,1,SEARCH("---",B1)+2,"")),A1,B1),
SEARCH("---",IF((REPLACE(A1,1,SEARCH("---",A1)+2,""))
(REPLACE(B1,1,SEARCH("---",B1)+2,"")),A1,B1))-1)&" +"&C1

(That's all one cell--paste in the formula bar with D1 selected)

wally wrote:

I have windows XP 2003 home edition.
I have a name and number in cell a1; john---22, I have a name in number
in b1; James---27, I have just a number in cell c1; "3" (ignore
quotes). In cell d1 I would like to show the name only (from cell a1 or
b1) that has the lowest number along with the number in cell c1. The
end result would look like this; John +3.
Here a sample of what my work sheet looks like and the result I am
seeking.
a1 b1 c1 d1
John---22 James---27 3 john +3
a2 b2 c2 d2
Joe---90 Betty---60 4 Betty +12
etc, etc down through a8:d8.
If a zero appears in column "c", then no name appears in column "d"
just a zero without the plus sign.
The range I am working with is a1:d8.
Thanks for any help you can provide.
WallyB

--

Dave Peterson

--

Dave Peterson

Here's another way:

This is an array formula and needs to entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(C1=0,0,CHOOSE(MATCH(MIN(--RIGHT(A1,2),--RIGHT(B1,2)),--RIGHT(A1:B1,2),0),LEFT(A1,LEN(A1)-5),LEFT(B1,LEN(B1)-5))&"
+"&C1)

Copy down as needed.

Returns:

John +3
Betty +4

Biff

"wally" wrote in message
oups.com...
Hi Biff, you did reply to my post about a month ago. I have used what
you sent me to great sucess, now I would like to refine it to one more
step. Yes, it should be Betty +4 and it will always 2 digits. The name
is followed by 3 dashes. If the values are the same, then the number
will show a zero in the "c" column. The "d" column will then have just
a plain zero, no name or + sign.
Example: John---22 James---22 0 0
Thanks, Wally

Biff wrote:
Betty +12

Shouldn't that be: Betty +4 ?

Also, what if the the values are the same:

John---22 James---22

Are the numbers always 2 digits:

John---22
James---27
Joe---90
Betty---60

This seems familiar to me. Did I reply to a post of yours about a month
or
so ago?

Biff

"wally" wrote in message
oups.com...
I have windows XP 2003 home edition.
I have a name and number in cell a1; john---22, I have a name in number
in b1; James---27, I have just a number in cell c1; "3" (ignore
quotes). In cell d1 I would like to show the name only (from cell a1 or
b1) that has the lowest number along with the number in cell c1. The
end result would look like this; John +3.
Here a sample of what my work sheet looks like and the result I am
seeking.
a1 b1 c1 d1
John---22 James---27 3 john +3
a2 b2 c2 d2
Joe---90 Betty---60 4 Betty +12
etc, etc down through a8:d8.
If a zero appears in column "c", then no name appears in column "d"
just a zero without the plus sign.
The range I am working with is a1:d8.
Thanks for any help you can provide.
WallyB

Biff, I have tried and I get an error message that states the formula
contains an error and highlights the 0- (that is, the zero & minus
sign).
wally
Biff wrote:
Here's another way:

This is an array formula and needs to entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(C1=0,0,CHOOSE(MATCH(MIN(--RIGHT(A1,2),--RIGHT(B1,2)),--RIGHT(A1:B1,2),0),LEFT(A1,LEN(A1)-5),LEFT(B1,LEN(B1)-5))&"
+"&C1)

Copy down as needed.

Returns:

John +3
Betty +4

Biff

"wally" wrote in message
oups.com...
Hi Biff, you did reply to my post about a month ago. I have used what
you sent me to great sucess, now I would like to refine it to one more
step. Yes, it should be Betty +4 and it will always 2 digits. The name
is followed by 3 dashes. If the values are the same, then the number
will show a zero in the "c" column. The "d" column will then have just
a plain zero, no name or + sign.
Example: John---22 James---22 0 0
Thanks, Wally

Biff wrote:
Betty +12

Shouldn't that be: Betty +4 ?

Also, what if the the values are the same:

John---22 James---22

Are the numbers always 2 digits:

John---22
James---27
Joe---90
Betty---60

This seems familiar to me. Did I reply to a post of yours about a month
or
so ago?

Biff

"wally" wrote in message
oups.com...
I have windows XP 2003 home edition.
I have a name and number in cell a1; john---22, I have a name in number
in b1; James---27, I have just a number in cell c1; "3" (ignore
quotes). In cell d1 I would like to show the name only (from cell a1 or
b1) that has the lowest number along with the number in cell c1. The
end result would look like this; John +3.
Here a sample of what my work sheet looks like and the result I am
seeking.
a1 b1 c1 d1
John---22 James---27 3 john +3
a2 b2 c2 d2
Joe---90 Betty---60 4 Betty +12
etc, etc down through a8:d8.
If a zero appears in column "c", then no name appears in column "d"
just a zero without the plus sign.
The range I am working with is a1:d8.
Thanks for any help you can provide.
WallyB

There is no 0- in the formula. I see you're accessing through Google Groups.
In the current version of Google Groups "garbage" gets inserted into the web
pages and can show up inside formulas. Try removing the "-". Google put that
there!

Here's a very small sample file:

Sample file: match_min.xls 14kb

http://cjoint.com/?jfxpyLc07C

I added a bit more error trapping in the sampe file.

Biff

"wally" wrote in message
oups.com...
Biff, I have tried and I get an error message that states the formula
contains an error and highlights the 0- (that is, the zero & minus
sign).
wally
Biff wrote:
Here's another way:

This is an array formula and needs to entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(C1=0,0,CHOOSE(MATCH(MIN(--RIGHT(A1,2),--RIGHT(B1,2)),--RIGHT(A1:B1,2),0),LEFT(A1,LEN(A1)-5),LEFT(B1,LEN(B1)-5))&"
+"&C1)

Copy down as needed.

Returns:

John +3
Betty +4

Biff

"wally" wrote in message
oups.com...
Hi Biff, you did reply to my post about a month ago. I have used what
you sent me to great sucess, now I would like to refine it to one more
step. Yes, it should be Betty +4 and it will always 2 digits. The name
is followed by 3 dashes. If the values are the same, then the number
will show a zero in the "c" column. The "d" column will then have just
a plain zero, no name or + sign.
Example: John---22 James---22 0 0
Thanks, Wally

Biff wrote:
Betty +12

Shouldn't that be: Betty +4 ?

Also, what if the the values are the same:

John---22 James---22

Are the numbers always 2 digits:

John---22
James---27
Joe---90
Betty---60

This seems familiar to me. Did I reply to a post of yours about a
month
or
so ago?

Biff

"wally" wrote in message
oups.com...
I have windows XP 2003 home edition.
I have a name and number in cell a1; john---22, I have a name in
number
in b1; James---27, I have just a number in cell c1; "3" (ignore
quotes). In cell d1 I would like to show the name only (from cell a1
or
b1) that has the lowest number along with the number in cell c1. The
end result would look like this; John +3.
Here a sample of what my work sheet looks like and the result I am
seeking.
a1 b1 c1 d1
John---22 James---27 3 john +3
a2 b2 c2 d2
Joe---90 Betty---60 4 Betty +12
etc, etc down through a8:d8.
If a zero appears in column "c", then no name appears in column "d"
just a zero without the plus sign.
The range I am working with is a1:d8.
Thanks for any help you can provide.
WallyB

Bill,
The formula works great. I copied the formula just as you wrote it. I
didn't realize Google inserts garbage. It works great, thanks alot for
all of your help.Wally
Biff wrote:
There is no 0- in the formula. I see you're accessing through Google Groups.
In the current version of Google Groups "garbage" gets inserted into the web
pages and can show up inside formulas. Try removing the "-". Google put that
there!

Here's a very small sample file:

Sample file: match_min.xls 14kb

http://cjoint.com/?jfxpyLc07C

I added a bit more error trapping in the sampe file.

Biff

"wally" wrote in message
oups.com...
Biff, I have tried and I get an error message that states the formula
contains an error and highlights the 0- (that is, the zero & minus
sign).
wally
Biff wrote:
Here's another way:

This is an array formula and needs to entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(C1=0,0,CHOOSE(MATCH(MIN(--RIGHT(A1,2),--RIGHT(B1,2)),--RIGHT(A1:B1,2),0),LEFT(A1,LEN(A1)-5),LEFT(B1,LEN(B1)-5))&"
+"&C1)

Copy down as needed.

Returns:

John +3
Betty +4

Biff

"wally" wrote in message
oups.com...
Hi Biff, you did reply to my post about a month ago. I have used what
you sent me to great sucess, now I would like to refine it to one more
step. Yes, it should be Betty +4 and it will always 2 digits. The name
is followed by 3 dashes. If the values are the same, then the number
will show a zero in the "c" column. The "d" column will then have just
a plain zero, no name or + sign.
Example: John---22 James---22 0 0
Thanks, Wally

Biff wrote:
Betty +12

Shouldn't that be: Betty +4 ?

Also, what if the the values are the same:

John---22 James---22

Are the numbers always 2 digits:

John---22
James---27
Joe---90
Betty---60

This seems familiar to me. Did I reply to a post of yours about a
month
or
so ago?

Biff

"wally" wrote in message
oups.com...
I have windows XP 2003 home edition.
I have a name and number in cell a1; john---22, I have a name in
number
in b1; James---27, I have just a number in cell c1; "3" (ignore
quotes). In cell d1 I would like to show the name only (from cell a1
or
b1) that has the lowest number along with the number in cell c1. The
end result would look like this; John +3.
Here a sample of what my work sheet looks like and the result I am
seeking.
a1 b1 c1 d1
John---22 James---27 3 john +3
a2 b2 c2 d2
Joe---90 Betty---60 4 Betty +12
etc, etc down through a8:d8.
If a zero appears in column "c", then no name appears in column "d"
just a zero without the plus sign.
The range I am working with is a1:d8.
Thanks for any help you can provide.
WallyB

You have to be careful when you copy/paste formulas from Google Groups!

Thanks for the feedback!

Biff

"wally" wrote in message
oups.com...
Bill,
The formula works great. I copied the formula just as you wrote it. I
didn't realize Google inserts garbage. It works great, thanks alot for
all of your help.Wally
Biff wrote:
There is no 0- in the formula. I see you're accessing through Google
Groups.
In the current version of Google Groups "garbage" gets inserted into the
web
pages and can show up inside formulas. Try removing the "-". Google put
that
there!

Here's a very small sample file:

Sample file: match_min.xls 14kb

http://cjoint.com/?jfxpyLc07C

I added a bit more error trapping in the sampe file.

Biff

"wally" wrote in message
oups.com...
Biff, I have tried and I get an error message that states the formula
contains an error and highlights the 0- (that is, the zero & minus
sign).
wally
Biff wrote:
Here's another way:

This is an array formula and needs to entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(C1=0,0,CHOOSE(MATCH(MIN(--RIGHT(A1,2),--RIGHT(B1,2)),--RIGHT(A1:B1,2),0),LEFT(A1,LEN(A1)-5),LEFT(B1,LEN(B1)-5))&"
+"&C1)

Copy down as needed.

Returns:

John +3
Betty +4

Biff

"wally" wrote in message
oups.com...
Hi Biff, you did reply to my post about a month ago. I have used
what
you sent me to great sucess, now I would like to refine it to one
more
step. Yes, it should be Betty +4 and it will always 2 digits. The
name
is followed by 3 dashes. If the values are the same, then the number
will show a zero in the "c" column. The "d" column will then have
just
a plain zero, no name or + sign.
Example: John---22 James---22 0 0
Thanks, Wally

Biff wrote:
Betty +12

Shouldn't that be: Betty +4 ?

Also, what if the the values are the same:

John---22 James---22

Are the numbers always 2 digits:

John---22
James---27
Joe---90
Betty---60

This seems familiar to me. Did I reply to a post of yours about a
month
or
so ago?

Biff

"wally" wrote in message
oups.com...
I have windows XP 2003 home edition.
I have a name and number in cell a1; john---22, I have a name in
number
in b1; James---27, I have just a number in cell c1; "3" (ignore
quotes). In cell d1 I would like to show the name only (from cell
a1
or
b1) that has the lowest number along with the number in cell c1.
The
end result would look like this; John +3.
Here a sample of what my work sheet looks like and the result I
am
seeking.
a1 b1 c1 d1
John---22 James---27 3 john +3
a2 b2 c2 d2
Joe---90 Betty---60 4 Betty +12
etc, etc down through a8:d8.
If a zero appears in column "c", then no name appears in column
"d"
just a zero without the plus sign.
The range I am working with is a1:d8.
Thanks for any help you can provide.
WallyB