Automatically populate a table

tky672

New Member
Joined
Aug 18, 2017
Messages
3
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!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sure, but you need Excel 2016 in order to use the new function TEXTJOIN. Then, it's simple:


Book1
ABCD
1Proj_NameTierDivision
2Project A3Operations
3Project B3Revenue
4Project C1Operations
5Project D2Administrative
6Project E3Revenue
7Project F1Administrative
8Project G2Operations
9Project H3Operations
10
11123
12OperationsProject CProject GProject A, Project H
13RevenueProject B, Project E
14AdministrativeProject FProject D
Sheet28
Cell Formulas
RangeFormula
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top