Combine lookup and Concatenate functions

[etalkmtb]

I have a worksheet with two columns, Grps and Mems. Mems column
contain list of names, each name is a member of multiple grps.
see Figure A
*****************Figure C ********
********Figure B******
***Figure A***
Grps: Mems: Mems1: Grps1:
RN-Ex Alba Alba RN-Ex
RN-Us pual Pual RN-Us
RN-Us Alex Alex RN-us
RN-BO Amy Amy RN-BO, RN-Ex
RN-Ex Amy John RN-Br, RN-us
RN-Br John Sara RN-BN, RN-us
RN-Us John Justin RN-us
RN-BN Sara Anselme RN-BO, RN-ex,RN-us
RN-Us Sara
RN-Us Justin
RN-BO Anselme
RN-Ex Anselme
RN-Us Anselme

Figure B, I added Mems1 column using Advance filter, copy to another
location and unique unique records only switches.

Figure C is where I would like to end up with, but don't know how to
properly nest/combine If, lookup, match and concatenate formulas.


Any idea?


Thanks -Mike

[Domenic]

If instead of combining the results into one cell you don't mind having them returned to individual cells, try the following...

1) Select five cells (since you have five different groups), for example D2 to H2

2) With those cells highlighted, enter the following formula:

=IF(COLUMN()-COLUMN($D2)+1<=COUNTIF($B$2:$B$14,C2),INDEX($A$2:$A$14,SMALL(IF($B$2:$B$14=C2,ROW($B$2:$B$14)-CELL("row",$B$2)+1),TRANSPOSE(ROW(INDIRECT("1:"&COUNTIF($B$2:$B$14,C2)))))),"")

...confirmed with CONTROL+SHIFT+ENTER.

3) Select D2:H2 and copy down

Hope this helps!

I have a worksheet with two columns, Grps and Mems. Mems column
contain list of names, each name is a member of multiple grps.
see Figure A
*****************Figure C ********
********Figure B******
***Figure A***
Grps: Mems: Mems1: Grps1:
RN-Ex Alba Alba RN-Ex
RN-Us pual Pual RN-Us
RN-Us Alex Alex RN-us
RN-BO Amy Amy RN-BO, RN-Ex
RN-Ex Amy John RN-Br, RN-us
RN-Br John Sara RN-BN, RN-us
RN-Us John Justin RN-us
RN-BN Sara Anselme RN-BO, RN-ex,RN-us
RN-Us Sara
RN-Us Justin
RN-BO Anselme
RN-Ex Anselme
RN-Us Anselme

Figure B, I added Mems1 column using Advance filter, copy to another
location and unique unique records only switches.

Figure C is where I would like to end up with, but don't know how to
properly nest/combine If, lookup, match and concatenate formulas.


Any idea?


Thanks -Mike