Name & Number in one cell.

[wally]

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]

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]

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

 

[Dave Peterson]

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)

 

[Dave Peterson]

=if(c1=0,0, thatlongformula)

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

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

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

 

[Biff]

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]

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).

 

[Biff]

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]

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

 

[wally]

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]

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

Thanks for the feedback!

Biff

 

[Biff]

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

Thanks for the feedback!

Biff