Defining the Mission
Let’s begin by examining the data then discuss the result we are hoping to achieve.
We have a dataset with three columns: Project, Department, and Person.
The data is in a proper Excel Table format where we have given the table the name “TProject”.
Our objective is to create a two-way table (officially known as a cross-tabular structure); one that lists the Projects to the left and the Departments across the top.
At the intersection of each “Project/Department” combination we wish to see all Persons assigned to the said combination.
Here’s the catch. A typical two-way table would list a separate “Project/Department” entry for each associated Person.
Our table should have but a single entry for each “Project/Department” and list all Persons in a single cell.
We will examine 3 methods for achieving this result, each using a different toolset:
- Formulas
- Power Query
- DAX
I’ll leave it up to you to decide which method works best for you.
Featured Course
Black Belt Excel Package
Method #1 – Using Formulas
As the two-way table has 3 main components (Project, Department, and Person), we will construct the result in 3 steps.
Step 1: Unique Projects using UNIQUE
We begin by making a list of unique projects. This is accomplished using Excel’s UNIQUE function.
The UNIQUE function will reduce the list of Projects into a single unique list.
Select cell E3 and enter the following formula:
=UNIQUE(TProject[Project])
BONUS FORMULA: If you want your result sorted in alphabetical order, place the previous formula inside a SORT function.
=SORT(UNIQUE(TProject[Project]))
Step 2: Unique Departments using UNIQUE and TRANSPOSE
Next, We make a list of unique departments using Excel’s UNIQUE function.
Because we want the results to be listed “left to right”, we will place the UNIQUE function inside a TRANSPOSE function to perform the “flip”.
Select cell F2 and enter the following formula:
=TRANSPOSE(UNIQUE(TProject[Department]))
As before, you can also integrate the SORT function into this formula to produce an alphabetically sorted list of departments.
=TRANSPOSE(SORT(UNIQUE(TProject[Department])))
Step 3: List Associated Persons for each “Project/Department” Combination
This step is trickier than it appears because some “Project/Department” combinations have more than one Person associated with them.
How can we return every associated Person in a single cell for a given “Project/Department” combination?
Start by thinking of a function that can return multiple results for given criteria.
The FILTER function works well for this need.
In cell F3, write the following FILTER function:
=FILTER(TProject[Person], TProject[Project] & TProject[Department] = E3 & F2, "")Because we wish to fill this formula down and to the right to create an array of results, we need to “fix” or “lock” the references so that one part of the reference can be modified when copied while the other part remains unchanged.
The modified formula with mixed reference appears as follows.
=FILTER(TProject[Person], TProject[Project] & TProject[Department] = $E3 & F$2, "")
Note: The two sets of double quotes at the end tell FILTER to show nothing if there are no results for a given “Project/Department” combination.
Dealing with Multiple Results
If we examine the results for “Grand Slam” and “Sales” (cell F3), we see that 2 names are returned.
Because the new Dynamic Array functions will “spill” results into adjacent cells, we need to come up with a way to combine all results into a single cell.
This is where the TEXTJOIN function comes to the rescue.
The TEXTJOIN function will take a list of items and chain them together with a delimiter (i.e., defined characters between each list item.) In our case, we want an ampersand symbol (&) with a leading and trailing space.
“ & ”
The update formula will be of the following structure:
=TEXTJOIN(" & ", TRUE, {previous FILTER function goes here} )NOTE: The TRUE tells TEXTJOIN to ignore empty cells. You could leave this out as it is the default behavior, but we’ll include it here just for completeness.
Update the previous formula to include the TEXTJOIN function
=TEXTJOIN(" & ", TRUE, FILTER(TProject[Person], TProject[Project] & TProject[Department] = $E3 & F$2, "") )
Repeating the Formula for the Remainder of the Table
Now that we have our formula working, let’s fill/repeat the formula down and across to the adjacent cells.
If we grab the Fill Series handle and fill down to cell F6, all seems right with the World.
But look what happens when we fill to the right for the remaining Departments.
We don’t see any results. This is because the references to the table columns are moving due to natural Relative Reference behavior.
When referencing columns in an Excel Table, the column name references will change (i.e., “move”) to the next column when using the Fill Series feature.
There is a formulaic way to deal with this, but it is a bit complicated and will cause our formula to increase in size. A simple fix for this is to manually copy/paste the formulas in cells F3:F6 into cells G3:G6 and cells H3:H6.
If you’re feeling adventurous, the below formula is how you would solve this problem in a way that would allow the Fill Series feature to operate as expected.
Don’t say I didn’t warn you.
=TEXTJOIN(" & ", TRUE, FILTER(TProject[[Person]:[Person]], TProject[[Project]:[Project]] & TProject[[Department]:[Department]] = $E3 & F$2, "") )Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
Method #2 – Using Power Query
Power Query is (it could be argued) the best feature in Excel you could devote time to learning.
Transforming “dirty” data into “clean” data is Power Query’s specialty.
Let’s see how Power Query can convert the data into the desired two-way table format.
Bringing the Data into Power Query
To bring the table of data into Power Query, click anywhere in the data and select Data (tab) -> Get & Transform Data (group) -> From Sheet (formerly labeled as “From Table/Range”).
You are presented with the Power Query Editor and a view of the imported data.
Pivoting the Department Column
Because we want the list of Departments across the top of the table, we can select the heading for Department and then click Transform (tab) -> Any Column (group) -> Pivot Column.
In the Pivot Column dialog box, we select Person as the “Values Column” (the column to be aggregated at each “Project/Department” intersection). Also, expand the “Advanced Options” and set the aggregation method to “Don’t Aggregate” and click OK.
The result is less than optimal. Clicking to the right of one of the ERROR entries, we can see in the Preview Window (bottom of the editor) that Power Query can’t display multiple results in a single cell.
We need to come up with a way to join all the related Person entries before performing the pivot column operation.
Since Power Query doesn’t have an Undo feature, we can remove the “Pivot Column” step by clicking the “X” to the left of the step listed in the Applied Steps panel.
Grouping the Persons by Project & Department
Grouping is a great way to create lists of items that share a common factor. In our case, we want to create a separate list of Person names for each “Project/Department” combination.
To do this, select the Project and Department headers (click on “Project”, then hold down CTRL and click on “Department”).
Next, select Home (tab) -> Transform (group) -> Group By.
Because we had pre-selected the “Project” and “Department” columns, these columns are pre-configured as columns to group by.
Change the name of the output column to “Results” and set the Operation to “All Rows”.
We are presented with a table of unique “Project/Department” combinations.
BONUS: Sorting the Projects and Departments
Before we pivot the Department column and create a two-way table, let’s get the list of projects and the list of departments sorted in alphabetical order.
Click the small down-arrow button on the Project column heading and select “Sort Ascending”.
Perform the same step on the Department column.
If you look closely, you can see small up arrows on the buttons and small numbers beside the buttons.
The arrows indicate sort direction (ascending) and the numbers indicate sort order.
All projects are listed in alphabetical order, and if duplicates projects exist, then each duplicated project will be sorted by department.
Creating the Combined Results
We’re still not ready to pivot the Departments because our Results column contains nested tables.
Clicking to the right of any of the nested “Table” entries reveals the complete table rows for the selected “Project/Department” combination.
Our goal is to combine all entries in the Person column of each nested table and display them in a single cell.
To do this, we will create a custom column and use a Power Query function to extract the names for each nested table.
Click Add Column (tab) -> General (group) -> Custom Column.
In the Custom Column dialog box, name the new column “Lists” and enter the following formula in the formula field:
=[Results][Person]
When we click OK, we see a new column with nested lists. Clicking to the right of a List reveals in the Preview Windows the extracted names for that “Project/Department” combination.
Now comes the part where we join the names together, separated by “ & “ characters as we did in the formulas example.
Click the small “left/right” arrow button at the top of the “Lists” column and select “Extract Values…”
In the “Extract values from list” dialog box, select –Custom– and enter the “space ampersand space” character combination. Click OK when finished.
The result is exactly what we are looking for.
Delete the “Results” column by clicking the column heading and press the Delete key on the keyboard.
Pivoting the Departments
Our final step in the creation of this two-way table is to select the Department column heading and execute the Pivot Column operation as we did earlier.
In the Pivot Column dialog box, select “Lists” as the Values Column (the column to be aggregated at each “Project/Department” intersection) and set the aggregation method to “Don’t Aggregate”.
Clicking OK reveals our perfectly formed, two-way table.
Sending the Results to Excel as a Table
To send the results back to Excel as a new table, click Home (tab) -> Close (group) -> Close & Load.
NOTE: If you add/remove/modify entries in the source data, you will need to right-click on the Power Query results and select “Refresh”.
Featured Course
Master Excel Power Query – Beginner to Pro
Method #3 – Using DAX Formulas & Power Pivot
Power Pivot is a data modeling technology that lets you create data models, establish relationships, and create calculations.”
Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel data models.
We will use Power Pivot and the associated DAX functions to create the two-way table.
Loading the Data into DAX
We begin by loading the data into the Power Pivot Data Model. This is done by clicking anywhere in the data and selecting Power Pivot (tab) -> Tables (group) -> Add to Data Model.
This opens the Power Pivot for Excel window where we can perform a variety of data mode activities.
Before we dive in and start creating measures in our model, let’s step back to “regular” Excel and create a Pivot Table.
From there, we will determine which measures need to be created. We will return to Power Pivot and create the magic.
Close the Power Pivot for Excel window and return to Excel.
Build a Pivot Table
Normally we would click in the data to create a Pivot Table, but since we have loaded the table into the Data Model we will click in the Pivot Table destination call and select Insert (tab) -> Tables (group) -> Pivot Table -> From Data Model.
Select the desired location for the and click OK. We are presented with an empty shell of a Pivot Table.
In the PivotTable Fields panel, place Project in the Rows section and Department in the Columns section.
We can’t place Person in the Values field as it will count the number of entries for each “Project/Department” combination.
Like the previous methods, we want to iterate through the table, concatenating each person with an identical “Project/Department” combination.
DAX has a function that can perform this type of activity; its name is CONCATENATEX.
Creating the Measure using CONCATENATEX
To create a measure in the Data Model that uses CONCATENATEX, right-click on the table name “TProject” in the PivotTable Fields panel and select “Add Measure…”
In the Measure dialog box, give the measure the name of “Person Allocation” and enter the below formula:
=CONCATENATEX(TProject, TProject[Person], " & ")
The CONCATENATEX function uses 3 arguments:
- The table of data (TProject)
- The expression (e., column to return) (TProject[Person])
- The delimiter to use between entries (“ & “)
Click OK to load the measure into the Data Model.
We now see the measure listed as a usable field in the PivotTable Fields panel.
Add the newly created “Person Allocation” measure to the Values field of the Pivot Table.
We see the results of the concatenated names in the Pivot Table (Sorry about it being tiny; it will look better soon.)
Fixing the Total Row and Total Column
We don’t want to see the totals for the names as it creates a very elongated and somewhat incorrect Pivot Table. Hence, we need to hide the Grand Totals for the rows and columns.
We have 2 ways to accomplish hiding action:
- Manually for a single table (ribbon controls)
- Automatically for every table that uses the measure (enhanced DAX formula)
Turn Off Grand Totals via the Ribbon
To use the ribbon to suppress the grand totals for the rows and columns, click in the Pivot Table and select Design (tab) -> Layout (group) -> Grand Totals -> Off for Rows and Columns.
The result is as follows.
Although easy to implement, every Pivot Table that uses the “Person Allocation” measure will need to have its Grand Totals manually suppressed.
Let’s automate this process with a modification to the CONCATENATEX formula.
Turn Off Grand Totals via the HASONEFILTER Function
We can use a DAX function called HASONEFILTER to detect when an item has or does not have a single filter being applied.
HASONEFILTER returns TRUE when the number of directly filtered values on a column is one; otherwise returns it returns a FALSE.
To modify the CONCATENATEX measure, right-click the “Person Allocation” measure and select “Edit Measure…”
We want to perform 2 tests:
- Is the item on the [Project] row being filtered by a single criterion?
- Is the item in the [Department] column being filtered by a single criterion?
If both tests pass, we will execute the CONCATENATEX function.
=IF(AND(HASONEFILTER(TProject[Project]), HASONEFILTER(TProject[Department])), CONCATENATEX(TProject, TProject[Person], " & ") )
Because a Grand Total for rows (or columns) wouldn’t have a filter in place (it’s an unfiltered list), the function does not perform any action on those locations (i.e., grand total cells).
The advantage to this more complex method is that any Pivot Table that uses the “Person Allocation” measure will automatically suppress its Grand Total rows/columns. If additional items are included in the Pivot Table, those items will display Grand Totals while the person names would remain suppressed.
Final Thoughts
If this post demonstrates anything it’s that Excel can solve the same problem in a variety of different ways.
Whatever way you choose is up to you provided it achieves the desired result.
Each of these methods has its strengths and weaknesses. Select the method that works best for your level of knowledge, experience, and comfort.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
