How to get and concatenate values from one column based on another column in Excel

Question

Excel is beating me up for a day here.

I have this table:

+---+--------+--------+--------+--------+
|   |   A    |   B    |   C    |   D    |
+---+--------+--------+--------+--------+
| 1 |   AGE  |  EX#   |   DG1  |   DG2  |
+---+--------+--------+--------+--------+
| 2 |   19   |   C01  |  ASC   |        |
+---+--------+--------+--------+--------+
| 3 |   45   |   C02  |  ATR   |        |
+---+--------+--------+--------+--------+
| 4 |   27   |   C03  |  LSI   |        |
+---+--------+--------+--------+--------+
| 5 |   15   |   C04  |  LSI   |        |
+---+--------+--------+--------+--------+
| 6 |   49   |   C05  |  ASC   |  AGC   |
+---+--------+--------+--------+--------+
| 7 |   76   |   C06  |  AGC   |        |
+---+--------+--------+--------+--------+
| 8 |   33   |   C07  |  ASC   |        |
+---+--------+--------+--------+--------+
| 9 |   17   |   C08  |  LSI   |        |
+---+--------+--------+--------+--------+

Now, I need to create a new table based on that data, with one row and one column, which I'll fill column A and need a formula to fill column B:

+----+--------+---------------+
|    |   A    |        B      |
+--=-+--------+---------------+
|    |   DG   |       AGE     |
+--=-+--------+---------------+
| 10 |   AGC  |     49, 76    |
+----+--------+---------------+
| 11 |   ASC  |   19, 33, 49  |
+----+--------+---------------+
| 12 |   ATR  |       45      |
+----+--------+---------------+
| 13 |   LSI  |   15, 17, 27  |
+----+--------+---------------+

So I need a formula to check the first table's columns C and D for each DGs, and check the age of each one in column A, and then concatenate all values that match into one cell with a , as a separator.

Can anyone help me?

Thanks

Answer 1

On the great excel website from Chip Pierson, I found the custom function: StringConcat. Just copy-paste the code in a VBA module.

Something like the following formula (in cell B10 & fill down)should work for you. It's an array formula (commit with [ctrl-shift-enter]

=StringConcat(", ",IF(Sheet1!$B$2:$C$100=A10,Sheet1!$A$2:$A$100,""))

You'll have to adjust the ranges off course.