Excel concatenate strings from cells listed in third cell

Question

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.

Answer 1

Using something like this could work for your HTTP,HTTPS,DNS cell:

=IF(COUNTIF(G3, "*"&H7&"*") > 0, I7)&", "&IF(COUNTIF(G3, "*"&H8&"*") > 0, I8)&", "&IF(COUNTIF(G3, "*"&H9&"*") > 0, I9)

In the example: G3 is your 1,2,3 cell and column H is your # reference and I is your protocol.

It's ugly as sin, but that seems to be the norm with Excel.