if (d6 contains 1.5 then c6 else "")

[Kay Schulz]

Hi all,

I have a problem which I cannot figure out.
I have a table like this:
a 1.6
b 1.5, 1.6
c 1.4, 1.5, 1.6
d 1.9
e 1.7

Now I want to create a table which looks like this:
1.4 c
1.5 b, c
1.6 a, b, c
1.7 e
1.8
1.9 d

Now i thought I can create the list 1.1-1.10
and then do something like this:
=if (b1="1.6"; a1; "")
Works
but only solves the fields where I have one entry only.
So i must find something like this:
=if(b1 contains "1.6"; a1; "")

but don't know how to do it.
Any idea?
Thanks
Kay

 

[macropod]

Hi Kay,

Assuming
(a) everything starts on row 1,
(b) your first table's letters are in Column A and
(c) its values are in column B
then, if you create a column of lookup values in Column C and put the
following formula in D1:
=SUBSTITUTE(TRIM(IF(ISERROR(FIND(C1,B$1)),"","a ")&
IF(ISERROR(FIND(C1,B$2)),"","b ")&IF(ISERROR(FIND(C1,B$3)),"","c ")&
IF(ISERROR(FIND(C1,B$4)),"","d ")& IF(ISERROR(FIND(C1,B$5)),"","e "))," ",",
")
and copy down as far as needed, you should get the results you're after.

Cheers