appending data to individual cell throug function

[tw]

can I do this???
=IF(FIND(B5,C21),B21 & ", ","") & IF(FIND(B5,C22),B22 & ", ","") &
IF(FIND(B5,C23),B23 & ", ","") & IF(FIND(B5,C24),B24 & ", ","") &
IF(FIND(B5,C25),B25 & ", ","") & IF(FIND(B5,C26),B26 & ", ","") &
IF(FIND(B5,C27),B27 & ", ","") & IF(FIND(B5,C28),B28 & ", ","") &
IF(FIND(B5,C29),B29 & ", ","") & IF(FIND(B5,C30),B30 & ", ","") &
IF(FIND(B5,C31),B31 & ", ","") & IF(FIND(B5,C32),B32 & ", ","") &
IF(FIND(B5,C33),B33 & ", ","") & IF(FIND(B5,C34),B34 & ", ","") &
IF(FIND(B5,C35),B35 & ", ","") & IF(FIND(B5,C36),B36 & ", ","") &
IF(FIND(B5,C37),B37 & ", ","") & IF(FIND(B5,C38),B38 & ", ","") &
IF(FIND(B5,C39),B39 & ", ","") & IF(FIND(B5,C40),B40 & ", ","") &
IF(FIND(B5,C41),B41 & ", ","") & IF(FIND(B5,C42),B42 & ", ","") &
IF(FIND(B5,C43),B43 & ", ","") & IF(FIND(B5,C44),B44 & ", ","")

What I want to accomplish is creating a list of groups that any one person
belongs to. Spreadsheet looks like this

b5 = 'chuck'
b6 = 'terry'
b7 = 'mike'
b8 = 'kathy'

b21 = 'groupname1'
c21 = 'chuck, terry, mike, kathy'

b22 = 'groupname2'
c22 = 'chuck, mike, kathy'

b23 = 'groupname3'
c23 = 'mike, terry, kathy'

I would like to use a formula in the cells corresponding to the person -
cells c5, c6, and c7 should show
c5 = 'groupname1, groupname2'
c6 = 'groupname1, groupname3'
c7 = 'groupname1, groupname2'
c8 = 'groupname1, groupname2, groupname3'

I got an error in the formula above. Once it is perfected I could put a
left(formula,len(c5)-2) to remove the extra ", ", but I can't seem to be
able to get that function to work. Does anyone have a suggestion?

 

[Trevor Shuttleworth]

You are getting a #VALUE! because the find will fail when the name is not
present in a group ... which could be the majority of cases. You'll need to
turn the formula round a little and make the group members cell absolute.

For example:

=IF(ISERROR(FIND(B5,$C$21)),"",$B$21 & ", ") &
IF(ISERROR(FIND(B5,$C$22)),"",$B$22 & ", ") &
IF(ISERROR(FIND(B5,$C$23)),"",$B$23 & ", ") ...

I'll leave the rest to you

Well, seeing as I've started and it's a real pain ... you need to remove any
unnecesary spaces too:


=IF(ISERROR(FIND(B5,$C$21)),"",$B$21& ",
")&IF(ISERROR(FIND(B5,$C$22)),"",$B$22&", ")&
IF(ISERROR(FIND(B5,$C$23)),"",$B$23&",
")&IF(ISERROR(FIND(B5,$C$24)),"",$B$24&", ")&
IF(ISERROR(FIND(B5,$C$25)),"",$B$25&",
")&IF(ISERROR(FIND(B5,$C$26)),"",$B$26&", ")&
IF(ISERROR(FIND(B5,$C$27)),"",$B$27&",
")&IF(ISERROR(FIND(B5,$C$28)),"",$B$28&", ")&
IF(ISERROR(FIND(B5,$C$29)),"",$B$29&",
")&IF(ISERROR(FIND(B5,$C$30)),"",$B$30&", ")&
IF(ISERROR(FIND(B5,$C$31)),"",$B$31&",
")&IF(ISERROR(FIND(B5,$C$32)),"",$B$32&", ")&
IF(ISERROR(FIND(B5,$C$33)),"",$B$33&",
")&IF(ISERROR(FIND(B5,$C$34)),"",$B$34&", ")&
IF(ISERROR(FIND(B5,$C$35)),"",$B$35&",
")&IF(ISERROR(FIND(B5,$C$36)),"",$B$36&", ")&
IF(ISERROR(FIND(B5,$C$37)),"",$B$37&",
")&IF(ISERROR(FIND(B5,$C$38)),"",$B$38&", ")&
IF(ISERROR(FIND(B5,$C$39)),"",$B$39&",
")&IF(ISERROR(FIND(B5,$C$40)),"",$B$40&", ")&
IF(ISERROR(FIND(B5,$C$41)),"",$B$41&",
")&IF(ISERROR(FIND(B5,$C$42)),"",$B$42&", ")&
IF(ISERROR(FIND(B5,$C$43)),"",$B$43&",
")&IF(ISERROR(FIND(B5,$C$44)),"",$B$44&", ")

Regards

Trevor

 

[tw]

thanks that did it

You are getting a #VALUE! because the find will fail when the name is not
present in a group ... which could be the majority of cases. You'll need
to turn the formula round a little and make the group members cell
absolute.

For example:

=IF(ISERROR(FIND(B5,$C$21)),"",$B$21 & ", ") &
IF(ISERROR(FIND(B5,$C$22)),"",$B$22 & ", ") &
IF(ISERROR(FIND(B5,$C$23)),"",$B$23 & ", ") ...

I'll leave the rest to you

Well, seeing as I've started and it's a real pain ... you need to remove
any unnecesary spaces too:


=IF(ISERROR(FIND(B5,$C$21)),"",$B$21& ",
")&IF(ISERROR(FIND(B5,$C$22)),"",$B$22&", ")&
IF(ISERROR(FIND(B5,$C$23)),"",$B$23&",
")&IF(ISERROR(FIND(B5,$C$24)),"",$B$24&", ")&
IF(ISERROR(FIND(B5,$C$25)),"",$B$25&",
")&IF(ISERROR(FIND(B5,$C$26)),"",$B$26&", ")&
IF(ISERROR(FIND(B5,$C$27)),"",$B$27&",
")&IF(ISERROR(FIND(B5,$C$28)),"",$B$28&", ")&
IF(ISERROR(FIND(B5,$C$29)),"",$B$29&",
")&IF(ISERROR(FIND(B5,$C$30)),"",$B$30&", ")&
IF(ISERROR(FIND(B5,$C$31)),"",$B$31&",
")&IF(ISERROR(FIND(B5,$C$32)),"",$B$32&", ")&
IF(ISERROR(FIND(B5,$C$33)),"",$B$33&",
")&IF(ISERROR(FIND(B5,$C$34)),"",$B$34&", ")&
IF(ISERROR(FIND(B5,$C$35)),"",$B$35&",
")&IF(ISERROR(FIND(B5,$C$36)),"",$B$36&", ")&
IF(ISERROR(FIND(B5,$C$37)),"",$B$37&",
")&IF(ISERROR(FIND(B5,$C$38)),"",$B$38&", ")&
IF(ISERROR(FIND(B5,$C$39)),"",$B$39&",
")&IF(ISERROR(FIND(B5,$C$40)),"",$B$40&", ")&
IF(ISERROR(FIND(B5,$C$41)),"",$B$41&",
")&IF(ISERROR(FIND(B5,$C$42)),"",$B$42&", ")&
IF(ISERROR(FIND(B5,$C$43)),"",$B$43&",
")&IF(ISERROR(FIND(B5,$C$44)),"",$B$44&", ")

Regards

Trevor