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
