Imagine you’re organizing a party for the major Game of Thrones characters (or maybe just your office crowd).  Your event would include food, so in order to ensure everybody is served food they enjoy, your RSVP form would include a question where your guests can select what type of meal they want. (This post is not about how to set up and collect this data. You could use Outlook voting buttons, an Excel Online survey or something like that.)

Once all people have returned their RSVPs, you can use Excel to organise the replies into a list with two columns: Name and Meal choice. Something like this:

The name of the table is MealOrders.

Now you need to work out how many meals of each type you need to order from your catering company.

You could build a pivot table, but since there is a fixed number of just seven meal choices, we can also just build a simple table with a formula. Place the seven meal choices into seven cells and use a Countif function. Turn that into a table by pressing Ctrl-T or click Insert > Table. (Tables are great for working with data!!)

Something like this: The blue table has the user data, the green table has the count per meal choice.

The formula to calculate how many meals to order (in the green table) is a simple Countif function, which you can see in the formula bar, but here it is if you want to copy it:

=COUNTIF(MealOrders[meal choice],[@[meal chosen]])

In plain words: In the table MealOrders (the blue table), look at the column meal choice and count how often the value of the column meal chosen from the current table (the green table) appears.

So far, so good. You know how many meals of each type to order to keep your GOT team happy, which is, as we all know, not easy at times.

Now the catering company delivers the food. You need to instruct your wait staff to deliver the right meal to the right person. Let’s say you have one waiter for each meal type. How can you work out who has ordered which meal, so you can give each of the wait staff a distinct list of people to deliver these meals to? One option is to sort the MealOrder table by the column meal choice. That will still result in quite a long list and require some scrolling to see all the data.

What if there were a way to list all names for kosher meals in one cell?  If you’ve been using Excel for a while, you know that this may not be so easy, but, …. drum roll ….

— Let me take you on a little excursion —

Excel 2016 with an Office 365 subscription has a new function called TextJoin().  The syntax for TextJoin is

=Textjoin(Delimiter, IgnoreBlank, Range)

Delimiter is a text string. You can specify any delimiter you want, and the delimiter can be more than one character. So, if you want to separate list entries with a comma and a space, then use a comma and a space, like “, “.

IgnoreBlank will take TRUE or FALSE. If the source data has blank cells and you want to ignore these, select TRUE.

Range is the list of cells that holds the values that you want to join or concatenate. \

Here are two examples:

You see that you can use cell references (formula in cell A9, written out in cell A10) as well as Structured References (formula in cell A12, written out in cell A13) like table names and table columns in the TextJoin function. With the IgnoreBlank parameter set to TRUE, blanks don’t feature in the result.

— That’s the end of our excursion. Thank you for taking the time. —

Now, back to our hungry Game of Thrones crowd.

Using TextJoin, we can create a comma separated list of characters for each meal choice in a single cell. But it requires a little twist.

Let’s add a column next to the How many column. We need to feed (pardon the pun) the TextJoin function only those cells that have the meal choice listed in our meal chosen column of the same row.

As the Range parameter, we need to use an IF function. If the meal choice in the MealOrder table is the same text as the meal chosen column in the current row, then return the Name from the MealOrders table, otherwise, return a blank cell.

So the parameter for the Range looks like this:

IF(MealOrders[meal choice]=[@[meal chosen]],MealOrders[Name],"")

This is not a complete formula, just the parameter for the Range. This IF function will return a list (or array) of values, which the TextJoin function can then interpret.  Let’s go back to the unsorted list of orders:

If we apply the IF function above to the meal choice column when looking at “regular”, then the IF function will return an array that starts like this:

{"","Robert","","Catelyn","","","Jorah","Petyr".......}

For every name that sits next to the “regular” meal choice, the list will show the name. For all other meal choices, the list will show a blank.

In the TextJoin function, we’re asking to ignore the blanks when we set the second parameter to TRUE, remember?

=Textjoin(Delimiter, IgnoreBlank, Range)

So that’s what we will exploit for the solution.

In the results table with the column for How many meals we need to order, we can add another column to show who these meals need to be delivered to in a nice, concise, comma separated list. Like this:

Here is the formula:

=TEXTJOIN(", ",TRUE,IF(MealOrders[meal choice]=[@[meal chosen]],MealOrders[Name],""))

Now, here comes the catch. You’ve been waiting for that, haven’t you? It was too good to be true, right?

The catch is that the nested IF function returns an array, i.e. multiple values, to the TextJoin function. This makes the whole formula in cell F3 a so-called array formula. In order to show all results properly, an array formula must be confirmed by holding down both the Ctrl and the Shift keys and then pressing Enter.

This special way of confirming a formula in a cell will enable the TextJoin function to check out all the values that the If function comes up with. If the formula is confirmed with just hitting Enter, then only the first value of the array (or list) served by the nested IF function will be looked at.

You can tell if a formula has been confirmed with Ctrl-Shift-Enter, because if it has, it will be wrapped in curly braces in the formula bar. Check the screenshot. Do you see the curly braces? They are easy to overlook but essential for an array formula to work.

Every time you edit an array formula, you have to remember to press Ctrl-Shift-Enter!!

If you forget that keystroke, all the formulas in the table will deliver wrong results. The wrath of Daenerys will be upon you and her dragons will engulf you in fire. To avoid that, you may want to download the sample file and check out how all this works.

Download the sample file