I have a list that includes many-to-one relationships. I need to turn this
into a one-to-one relationship by concatenating the values of each of the
multiples. For example:

ID Name CountID
07 cat 3
07 dog 3
07 pig 3
12 fish 1
19 apple 2
19 grape 2

I can put my formula to the right, so I'm hoping toe end up with:

ID Name Count Concatenated
07 cat 3 cat, dog, pig
07 dog 3
07 pig 3
12 fish 1 fish
19 apple 2 apple, grape
19 grape 2

I can use a match statement against the ID to only populate rows with a new
ID (that's easy) but I haven't figured out a way to concatentate "the cell to
the left, and N cells down" based on the count column.

Any ideas?

Thanks!
Keith

Download and install free add-in from
http://download.cnet.com/Morefunc/30...-10423159.html

then use this formula

=IF(COUNTIF($A$2:A2,A2)=1,SUBSTITUTE(TRIM(MCONCAT( IF($A$2:$A$7=A2,$B$2:$B$7&" ","")))," ",", "),"")

ctrl+shift+enter, not just enter


"ker_01" wrote:

I have a list that includes many-to-one relationships. I need to turn this
into a one-to-one relationship by concatenating the values of each of the
multiples. For example:

ID Name CountID
07 cat 3
07 dog 3
07 pig 3
12 fish 1
19 apple 2
19 grape 2

I can put my formula to the right, so I'm hoping toe end up with:

ID Name Count Concatenated
07 cat 3 cat, dog, pig
07 dog 3
07 pig 3
12 fish 1 fish
19 apple 2 apple, grape
19 grape 2

I can use a match statement against the ID to only populate rows with a new
ID (that's easy) but I haven't figured out a way to concatentate "the cell to
the left, and N cells down" based on the count column.

Any ideas?

Thanks!
Keith

Hello,

I would NOT use that "free" add-in (why? See
http://www.sulprobil.com/html/excel_don_ts.html
Its my first Excel Don't) but suggest to use my UDF Cfreq:
http://www.sulprobil.com/html/cfreq.html

Regards,
Bernd

I have a list that includes many-to-one relationships. I need to turn
this into a one-to-one relationship by concatenating the values of
each of the multiples. For example:

ID Name CountID
07 cat 3
07 dog 3
07 pig 3
12 fish 1
19 apple 2
19 grape 2

I can put my formula to the right, so I'm hoping toe end up with:

ID Name Count Concatenated
07 cat 3 cat, dog, pig
07 dog 3
07 pig 3
12 fish 1 fish
19 apple 2 apple, grape
19 grape 2

Here's one way.

Start with the data in columns A, B, C, and use row 1 as the header row.

In D2, put
=IF(A2=A3,B2&", "&D3,B2)
and copy down as far as needed.

This gets the first line you wanted for each group, but has extra clutter
in between those first lines. The clutter can be hidden using conditional
formatting. Select D2 and use
Format Conditional formatting Formula Is
=A2=A1
and for the "Format" choose a font color of white.

Using the paint-brush button in the toolbar, copy this format to all of
column D.