Create a separated by comma from column list Excel

0
votes

I'm looking to create a separated by comma values from a column list, i have a condition that for not_simple cell do not concatenate, is there a way, a formula to create a new cell with a comma separated list from a column array ?

Below showing an example of excel tables and the result i need.

http://postimg.org/image/ql7gi4z59/enter image description here

Any help is very appreciated !

2
excel

2 Answers

1
votes

In B9 enter:

=B2 & "," & B3 & "," & B4 & ","& B5 & ","&B6 & ","&B7 & "," & B8

Similar for your other cells.

EDIT#1

For a more general approach, use the following UDF

Public Function KonKatenate(rIn As Range) As String
    For Each r In rIn
        KonKatenate = KonKatenate & "," & r.Text
    Next r
    KonKatenate = Mid(KonKatenate, 2)
End Function

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=KonKatenate(B2:B8)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

1
votes

If you take my User Defined Function from this thread you should be able to use it in a worksheet to gather the correct concatenations. First grab that UDF and put it into a module as per the instructions on that page.

Next apply a Data ► Sort & Filter ► Filter on your data and filter column A for not simple.

Next you will want to switch to R1C1 cell addressing as the formula is the same for all cells in R1C1 but different relative cell addresses for A1 cell addressing. For Excel 2010 this is in Excel Options ► Formulas ► R1C1 Reference style (Alt+F,T then F then Alt+R then OK).

Paste the following formula into the data cell in the filtered range. According to your sample this would be R9C2 or B9.

=udf_stitch_Together(INDEX(C,MAX(INDEX(ROW(R1:R[-1])*(R1C1:R[-1]C1="not simple"),,),1)+1):INDEX(C, ROW(R[-1])), ",")

    R1C1 Concat formula

Since you are in R1C1 mode the same formula can be used for every cell in column B where column A is not simple. Fill down then fill right to C4 or column D. Remove the filter for results as above.

Another method of filling in the formulas might be by selecting your range then using GoTo ► Special ► Blanks. Put the formula into the first cell and hit Ctrl+Enter.

Go back to A1 cell addressing with the same method you used to enter R1C1 mode.

EDIT: The formula can be put into the first cell and then filled both down and right on the filtered data.