If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Concatenate N cells (where N is a worksheet value)
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 |
#2
|
|||
|
|||
Concatenate N cells (where N is a worksheet value)
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 |
#3
|
|||
|
|||
Concatenate N cells (where N is a worksheet value)
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 |
#4
|
|||
|
|||
Concatenate N cells (where N is a worksheet value)
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. |
Thread Tools | |
Display Modes | |
|
|