MrExcel.com - Your One Stop for Excel Tips & Solutions

conditional select

Posted by Manu on September 28, 2001 7:55 AM

I have a spreadsheet with columns: A,B,C(Fields)

I would like to populate D for every record with the value of C only if either A=B or AlikeB

All help and time is much appreciated.

Manu

Posted by Barrie Davidson on September 28, 2001 7:58 AM

Two questions for you Manu.
1. How do you define where A is like B? For example, is it on the first three characters?

2. What do you want in column D if there is no match?

BarrieBarrie Davidson

Posted by Aladin Akyurek on September 28, 2001 8:07 AM

Manu --

Although I can't imagine why you need this, the following would do the job:

In D1 enter: =IF(OR(A1=B1,ISNUMBER(SEARCH(IF(LEN(A1)<=LEN(B1),A1,B1),IF(LEN(A1)>=LEN(B1),B1,A1)))),C1,"")

which interprets A1 like B1 as one being a subtring within the other.

Aladin

Posted by Manu on September 28, 2001 8:11 AM


Thanks for responding Barrie.

1. A & B both have names in them therefore the character count in each could be any

2. It would be wonderful if D could display "No Match" in case of a no match between A & B

I hope this answers but feel free to ask for more and thank you much for taking the time.

Regards,

Manu

Posted by Barrie Davidson on September 28, 2001 8:14 AM

Using Aladin's formula

with a slight modification.

=IF(OR(A1=B1,ISNUMBER(SEARCH(IF(LEN(A1)<=LEN(B1),A1,B1),IF(LEN(A1)>=LEN(B1),B1,A1)))),C1,"No Match")Barrie Davidson

Posted by Manu on September 28, 2001 8:17 AM

-- Although I can't imagine why you need this, the following would do the job: In D1 enter: =IF(OR(A1=B1,ISNUMBER(SEARCH(IF(LEN(A1)<=LEN(B1),A1,B1),IF(LEN(A1)>=LEN(B1),B1,A1)))),C1,"") which interprets A1 like B1 as one being a subtring within the other. Aladin

To answer your question Aladin(hello there by the way..)

A Contains Names, C Contains their IDs, B is another listing of names from which I need to identify common values between A & B and populate IDs for common values in D.

Will your solution perform this?

Thanks for your time and attention Aladin


Posted by Aladin Akyurek on September 28, 2001 8:29 AM

-- : Although I can't imagine why you need this, the following would do the job

Yes, I believe so given my interpretation of your question. The formula compares A1 with B1, A2 with B2 etc. Moreover, John and Johnny will be treated as being alike by this formula.

If in doubt, post 10 rows of your data Thanks for your time and attention Aladin

You're welcome

Aladin