Automatically populate a table

[tky672]

Hi All,

I have to populate a 3x4 table that is a list of our corporate projects. The projects belong to only one Division and Tier at a time. Projects progress from Tier 3 to 2 to 1. The data is sorted like this:

[TABLE="width: 312"]
<tbody>[TR]
[TD]Proj_Name
[/TD]
[TD]Tier
[/TD]
[TD]Division
[/TD]
[/TR]
[TR]
[TD]Project A [/TD]
[TD]3
[/TD]
[TD]Operations
[/TD]
[/TR]
[TR]
[TD]Project B
[/TD]
[TD]3
[/TD]
[TD]Revenue
[/TD]
[/TR]
[TR]
[TD]Project C
[/TD]
[TD]1
[/TD]
[TD]Operations
[/TD]
[/TR]
[TR]
[TD]Project D
[/TD]
[TD]2
[/TD]
[TD]Administrative
[/TD]
[/TR]
[TR]
[TD]Project E
[/TD]
[TD]3
[/TD]
[TD]Revenue
[/TD]
[/TR]
[TR]
[TD]Project F
[/TD]
[TD]1
[/TD]
[TD]Administrative
[/TD]
[/TR]
[TR]
[TD]Project G
[/TD]
[TD]2
[/TD]
[TD]Operations
[/TD]
[/TR]
[TR]
[TD]Project H
[/TD]
[TD]3
[/TD]
[TD]Operations
[/TD]
[/TR]
</tbody>[/TABLE]

Does anyone know of a way to automatically populate a 3x3 table so the outcome looks like this?

[TABLE="width: 316"]
<tbody>[TR]
[TD][/TD]
[TD]Tier 1
[/TD]
[TD]Tier 2
[/TD]
[TD]Tier 3
[/TD]
[/TR]
[TR]
[TD]Operations
[/TD]
[TD]Project C
[/TD]
[TD]Project G
[/TD]
[TD]Project A
Project H
[/TD]
[/TR]
[TR]
[TD]Revenue
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Project B
Project E
[/TD]
[/TR]
[TR]
[TD]Administrative
[/TD]
[TD]Project F
[/TD]
[TD]Project D
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

A couple of other notes: I’ve simplified the issue quite a bit. There are typically 300 projects in the list and they are constantly in flux. I need to be able to produce this 3x3 table at a moment’s notice, hence the need for automation. Also I would like the projects to be listed in one cell. For example, Project A and Project H are listed in the same cell. I am pretty tied to this format.

Thanks for your help!

 

[DRSteele]

Sure, but you need Excel 2016 in order to use the new function TEXTJOIN. Then, it's simple:

Book1
	A	B	C	D
1	Proj_Name	Tier	Division
2	Project A	3	Operations
3	Project B	3	Revenue
4	Project C	1	Operations
5	Project D	2	Administrative
6	Project E	3	Revenue
7	Project F	1	Administrative
8	Project G	2	Operations
9	Project H	3	Operations
10
11		1	2	3
12	Operations	Project C	Project G	Project A, Project H
13	Revenue			Project B, Project E
14	Administrative	Project F	Project D
Sheet28

Cell Formulas
Range	Formula
B12	{=TEXTJOIN(", ",1,IF(($A12=$C$2:$C$9)*(B$11=$B$2:$B$9),$A$2:$A$9,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[tky672]

This works! Thank you!

 

[DRSteele]

Super. You're welcome.

This can also be done in Power Query and in Pivot Tables, solutions that might be better than the array formula when your dataset is large. Let us know if you need that.