Creating a list of tasks in a sequence

[Innoguide]

I have 10 tasks that can each be assigned to 1 of ten sequence numbers. If there is more than 1 task assigned to a sequence number those tasks are run in parallel. So there can be anywhere from 1 to 10 tasks assigned to a sequence number but each task can only be assigned once.

The tasks are identified by the task numbers in column B and the sequence for the task is assigned in column A.

Does anyone have any suggestions for a formula in column E that will create a comma-separated list of the tasks assigned to each sequence number?

Note in the example that only 5 of the sequence numbers are used since some of the tasks run in parallel. The unused sequence numbers are blank.

Thanks in advance for any help!

A B C D E 1 Assigned Sequence Task Number Sequence Task List 2 1 212 1 212, 218 3 1 218 2 200, 202, 208, 210 4 2 200 3 204 5 2 202 4 206 6 2 208 5 214, 216 7 2 210 6 8 3 204 7 9 4 206 8 10 5 214 9 11 5 216 10 <tbody> </tbody>

<tbody>
</tbody>

 

[DRSteele]

You require Excel 2016 for this solution because it uses the new function TEXTJOIN.

	A	B	C	D	E
1	Assigned Sequence	Task Number		Sequence	Task List
2	1	212		1	212, 218
3	1	218		2	200, 202, 208, 210
4	2	200		3	204
5	2	202		4	206
6	2	208		5	214, 216
7	2	210		6
8	3	204		7
9	4	206		8
10	5	214		9
11	5	216		10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet33​

Array Formulas Cell Formula E2 {=TEXTJOIN(", ",,IF($A$2:$A$11=D2,$B$2:$B$11,""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

 

[Innoguide]

Wow DR Steele- very elegant solution. Works perfectly!

Thanks!

You require Excel 2016 for this solution because it uses the new function TEXTJOIN.

	A	B	C	D	E
1	Assigned Sequence	Task Number		Sequence	Task List
2	1	212		1	212, 218
3	1	218		2	200, 202, 208, 210
4	2	200		3	204
5	2	202		4	206
6	2	208		5	214, 216
7	2	210		6
8	3	204		7
9	4	206		8
10	5	214		9
11	5	216		10

<tbody>
</tbody>

Sheet33​

Array Formulas Cell Formula E2 {=TEXTJOIN(", ",,IF($A$2:$A$11=D2,$B$2:$B$11,""))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>

 

[DRSteele]

Splendid. You're welcome.

 

[Innoguide]

OK - This is strange. The formula works fine the first time, but it is not updating when I change the sequence numbers in column A

Calculation is set to auto and I've tried "Calculate Now", but the formula will not update unless I copy the cells over again.

Splendid. You're welcome.