=concatenate w/o assigning indv. cells

**udontsay** · 03-06-2010, 12:43 AM

I have a column with over 6000 entries and I want to =concatenate groups of 16 cells of the column into individual rows with the data field separated by a comma. i.e. a row of A1,A2,A3.....

Is there a way to =conatenate(text) a formula were it will automatically include the following 15 cells in a column after pasteing a formula in a cell, without having to assign the cells such as (A1:A16) along with the comma seperated field function

**DonkeyOte** · 03-06-2010, 03:51 AM

You should only need to do this once... ie first formula, eg:

B1:
=IF(MOD(ROWS(A$1:A1)-ROW(A$1),16),"",A1&","&A2&","&A3&","&A4&","&A5&","&A6&","&A7&","&A8&","&A9&","&A10&","&A11&","&A12&","&A13&","&A14&","&A15&","&A16)
copied down to B6000

will only generate strings as and when required (eg string, 15 blanks, string, 15 blanks, string...) and each string will reference current row and subsequent 15.

You can use VBA of course - but the above is pretty simple I'm sure you'll agree.

If you want the listing in consecutive cells (eg B1 (A1:A16), B2 (A17:A32) etc let us know)

**udontsay** · 03-06-2010, 06:35 PM

=IF(MOD(ROWS(A$1:A1)-ROW(A$1),16),"",A1&","&A2&","&A3&","&A4&","&A5&","&A6&","&A7&","&A8&","&A9&","&A10&","&A11&","&A12&","&A13&","&A14&","&A15&","&A16)
copied down to B6000

will only do the assigned A1-A16 cells no matter where it is pasted intead of the current row where pasted

**DonkeyOte** · 03-07-2010, 03:34 AM

Per my note, place the formula into B1 then copy it as you wish
The formula was designed such that it could be applied to all rows from B1 to last row of data in one go - it will only generate the strings every 16th row, using appropriate values.