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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

[tw]

thanks that did it
"Trevor Shuttleworth" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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?
>>
>
>