Excel concatenate strings from cells listed in third cell

Posted by Puddingfox on Super User See other posts from Super User or by Puddingfox
Published on 2010-03-29T20:51:47Z Indexed on 2010/03/29 20:53 UTC
Read the original article Hit count: 325

Filed under:
|

I have an excel 2007 workbook that has five columns:

A. A list of machines
B. A list of service numbers for each machine
C. A list of service names for each machine
...(nothing here)
I. A list of Service Numbers
J. A list of Service Names

Each machine listed in column A has one or more services running on it from the list in column J. I would like to be able to add services to a machine (i.e. updating the cell in Column C) by simply adding another comma-separated number to Column B. For Example, The first row would look like this assuming Machine1 has the first three services:

|    A     |  B    |    C
| Machine1 | 1,2,3 | HTTP,HTTPS,DNS

Right now I have to manually update the formula in column c for each change I make. The current formula is:

=CONCATENATE(J1,",",J2,",",J3)

I would like to use something like this (please forgive my syntax; I'm a coder and I'm treating cell B1 as if it is an indexed array):

=CONCATENATE(CELL("J"+B1[0] , "," , "J"+B1[1] , "," "J"+B1[2])

Although having variable numbers of services makes this even more difficult.

Is there any way of doing this. For reference, this is columns I and J:

|  I  |   J
|  1  |HTTP
|  2  |HTTPS
|  3  |DNS
.....
|  16 |Service16

I don't know very much about Excel so any help is greatly appreciated.

© Super User or respective owner

Related posts about excel

Related posts about excel-2007