How to find specified values among various fields.
Hello,
We need to write a formula which finds specified values among various fields. For example;
We wantto find the value "1A3" (string) in A1, A2, A3, A4, A5,...,A40 fields in a report and if there is one or more "1A3" values in any of these fields, our formula will take the value "S3".
Asking this because we need to search for lots of values like "1A3" in about 40 fields and assign a new value with this formula and it is really hard to do that by using the basic if-then-else structure. So we need 2 find out if there is a practical way.
Waiting for your urgent comments.
With Best Regards.
TAI
We need to write a formula which finds specified values among various fields. For example;
We wantto find the value "1A3" (string) in A1, A2, A3, A4, A5,...,A40 fields in a report and if there is one or more "1A3" values in any of these fields, our formula will take the value "S3".
Asking this because we need to search for lots of values like "1A3" in about 40 fields and assign a new value with this formula and it is really hard to do that by using the basic if-then-else structure. So we need 2 find out if there is a practical way.
Waiting for your urgent comments.
With Best Regards.
TAI
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
rdonline1 and Pete... Thank you so much for your help. Both answers are cool for me. I'll split points 2 both of you, but there's a problem with stringvar solution;
its working great up to 14 fields. but its not working more than 14?
its working great up to 14 fields. but its not working more than 14?
in that case u can try using
If InStr({A1} + {A2}+ {A3}+ {A4}+ ... + {A14},"1A3",1) >= 1 OR InStr({A15 + {A2}+ {A3}+ {A4}+ ... + {A29},"1A3",1) >= 1 OR InStr({A30} + {A2}+ {A3}+ {A4}+ ... + {A44},"1A3",1) >= 1Then
"S3"
Else
"1A3"
like this
rdonline1
If InStr({A1} + {A2}+ {A3}+ {A4}+ ... + {A14},"1A3",1) >= 1 OR InStr({A15 + {A2}+ {A3}+ {A4}+ ... + {A29},"1A3",1) >= 1 OR InStr({A30} + {A2}+ {A3}+ {A4}+ ... + {A44},"1A3",1) >= 1Then
"S3"
Else
"1A3"
like this
rdonline1
ASKER
thank you rdonline, but it would be gr8 if i could make it by using stringvar. because i'll search for lots of values in these fields and i dont want to see
i'd be appreciated if anyone could help about stringvar...
wbr.
i'd be appreciated if anyone could help about stringvar...
wbr.
Which version of CR are you using?
Pete
Pete
ASKER
Pete, I'm using CR XI.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
TAI - There shouldn't be a limit of the number of fields but there is on size. Are these fields very large? - frodoman
ASKER
Thank you bdreed35,
but it didnot work.. gives an error to formula; "the result of formula cannot be an array"...
but it didnot work.. gives an error to formula; "the result of formula cannot be an array"...
TAI-
Can you post the exact formula that you are using and then tell us where the highlighting starts when the error occurs?
Can you post the exact formula that you are using and then tell us where the highlighting starts when the error occurs?
ASKER
bdreed35,
its okay now, made a mistake and corrected it. Thank you.
Frodoman;
3 characters for each fields i dont know whats wrong but i saw some errors about stringvar solution. cause its not working for every record and i dont know why...
Looks like Using arrays solved my problem. Thank you all. I'll give 300 points to bdreed, 100 points to rdnline1 and 100 points to pete.
Thank you.
With best regards.
TAI
its okay now, made a mistake and corrected it. Thank you.
Frodoman;
3 characters for each fields i dont know whats wrong but i saw some errors about stringvar solution. cause its not working for every record and i dont know why...
Looks like Using arrays solved my problem. Thank you all. I'll give 300 points to bdreed, 100 points to rdnline1 and 100 points to pete.
Thank you.
With best regards.
TAI
ASKER
Noticed that all solutions are working correctly for 4 fields maximum; but not for more than 4 fields... it start to give blank values for more than 4 fields and it gives all blank values for more than 14 fields..
How can this happen? Any ideas?
Wbr
ASKER
created a view and joined all fields into one field and then used instr formulas to solve problem...
ty for instr formula...
ty for instr formula...
rdonline1