how to combine formulas to.........
 

I'd like to ask for help in the following problem:
col A contains alphanumeric values of different lenght (<20 characters) made
up to describe a different number of properties. Col B contains "key"s (also
alphanumeric).
I'd like to check, wheter the first part of a key of given (in D1) lenght
is included in any (which) of Ai (the proprties descriptor) and for how many
items the given key can be used.
Example:

colA: ColB:
rgt3huw57jfuhu; huw5cf9;
huwz57jkrt; 57jf8ac5;
o7jf800f; uwz5j9;
rgt8huw6hzz;....... 7jf8kol;........
if d1=3 then b1 will open a1, a2,and a4; b2 will open a1, a2; b3 will do
only a2; b4 will do only a1 and a3
if d1=4 then b1 will open a1, a2,; b2 will open a1; b3 will do a2; b4 only
a3.
Any suggestions ?

how to combine formulas to.........
 

Add this to C1 and copy down

=ISNUMBER(SEARCH(LEFT(B1,$D$1),A1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"gmisi" wrote in message
...
I'd like to ask for help in the following problem:
col A contains alphanumeric values of different lenght (<20 characters)
made
up to describe a different number of properties. Col B contains "key"s
(also
alphanumeric).
I'd like to check, wheter the first part of a key of given (in D1) lenght
is included in any (which) of Ai (the proprties descriptor) and for how
many
items the given key can be used.
Example:

colA: ColB:
rgt3huw57jfuhu; huw5cf9;
huwz57jkrt; 57jf8ac5;
o7jf800f; uwz5j9;
rgt8huw6hzz;....... 7jf8kol;........
if d1=3 then b1 will open a1, a2,and a4; b2 will open a1, a2; b3 will do
only a2; b4 will do only a1 and a3
if d1=4 then b1 will open a1, a2,; b2 will open a1; b3 will do a2; b4 only
a3.
Any suggestions ?

how to combine formulas to.........
 

gmisi wrote:
I'd like to ask for help in the following problem:
col A contains alphanumeric values of different lenght (<20 characters) made
up to describe a different number of properties. Col B contains "key"s (also
alphanumeric).
I'd like to check, wheter the first part of a key of given (in D1) lenght
is included in any (which) of Ai (the proprties descriptor) and for how many
items the given key can be used.
Example:

colA: ColB:
rgt3huw57jfuhu; huw5cf9;
huwz57jkrt; 57jf8ac5;
o7jf800f; uwz5j9;
rgt8huw6hzz;....... 7jf8kol;........
if d1=3 then b1 will open a1, a2,and a4; b2 will open a1, a2; b3 will do
only a2; b4 will do only a1 and a3
if d1=4 then b1 will open a1, a2,; b2 will open a1; b3 will do a2; b4 only
a3.
Any suggestions ?

With both properties and keys in columns I've placed it into the too
hard basket (probably doable though, just not by me)

However, I copied column A, the properties, and pasted them into column
D, directly below D1 (which holds the key length value). Then I copied
column B and did a paste special transpose, starting in E1, so that the
keys are now column headings (columns(E to H so far).

In E2 I used the formula...

=IF(ISERROR(FIND(LEFT(E$1,$D$1),$D2)),"","yes")

which I then filled across and down to fill the (currently) four*four
table.

Changing the value in D1 then creates a new set of table results. My
results do reveal that when key length = 4 that the first key does not
open the second property as you have claimed (huw5<huwc).

Ken Johnson

how to combine formulas to.........
 

Make that huw5<huwz

Ken Johnson

how to combine formulas to.........
 

Dear Ken,
Thank you, your idea helps me out from the dead-end street after making some
rearrangements on my sheets.

My sincere thanks to you and to all other contributors who are sharing their
shining knowledge of XL just to educate a bit such "woodheads" as me!!!!!!!


"Ken Johnson" wrote:

gmisi wrote:
I'd like to ask for help in the following problem:
col A contains alphanumeric values of different lenght (<20 characters) made
up to describe a different number of properties. Col B contains "key"s (also
alphanumeric).
I'd like to check, wheter the first part of a key of given (in D1) lenght
is included in any (which) of Ai (the proprties descriptor) and for how many
items the given key can be used.
Example:

colA: ColB:
rgt3huw57jfuhu; huw5cf9;
huwz57jkrt; 57jf8ac5;
o7jf800f; uwz5j9;
rgt8huw6hzz;....... 7jf8kol;........
if d1=3 then b1 will open a1, a2,and a4; b2 will open a1, a2; b3 will do
only a2; b4 will do only a1 and a3
if d1=4 then b1 will open a1, a2,; b2 will open a1; b3 will do a2; b4 only
a3.
Any suggestions ?

With both properties and keys in columns I've placed it into the too
hard basket (probably doable though, just not by me)

However, I copied column A, the properties, and pasted them into column
D, directly below D1 (which holds the key length value). Then I copied
column B and did a paste special transpose, starting in E1, so that the
keys are now column headings (columns(E to H so far).

In E2 I used the formula...

=IF(ISERROR(FIND(LEFT(E$1,$D$1),$D2)),"","yes")

which I then filled across and down to fill the (currently) four*four
table.

Changing the value in D1 then creates a new set of table results. My
results do reveal that when key length = 4 that the first key does not
open the second property as you have claimed (huw5<huwc).

Ken Johnson

how to combine formulas to.........
 

gmisi wrote:
Dear Ken,
Thank you, your idea helps me out from the dead-end street after making some
rearrangements on my sheets.

My sincere thanks to you and to all other contributors who are sharing their
shining knowledge of XL just to educate a bit such "woodheads" as me!!!!!!!


Hi gmisi,

You're welcome.
Thanks for the feedback

Ken Johnson