Add column with custom text to pivot table

Question

I have a large Excel (2016) file that looks like this

ID | Group       | Status      | ...
01 | Yamato    | OK           |
02 | Serenity   | Delayed   |
03 | Normandy| Failed      |
04 | Normandy| OK          |
05 | Serenity   | Idonteven|

On another sheet there is a pivot table that provides an overview of each group, specifically the status counts, but it's important for us to be able to add a comment for the whole group, e.g.:

Group    | Total | OK | Failed | Delayed | Idonteven | Comment
Serenity | 154  | 120 | 12      | 20           | 2              | [FFS] Insufficient supplies

Currently this is done by a dedicated column outside of the actual pivot table, far enough to the right to accommodate the columns that could be needed for all states. HOWEVER groups may be added or removed, and if a group is removed, the string for it is removed from the pivot table and all the rows below shift up, however comment column is not adjusted and the comments end up in the rows for the wrong groups.

So I wanted to check if anyone knows a way to add a column with custom text to a pivot table in a way that would keep the comments attached to the correct string, or another functionality to the same effect.

(Actual comments via the "New comment" button would not work here, as the tags in the comments are used to trigger formatting/formulas further downstream)

Answer 1

you can use 'Fuzzy Lookup' as well;
I create a Pivot table of data, I then use 'Fuzzy Lookup' to add Columns of text later.

Example; Supplier Name, Supplier CODE, need to be in the same pivot table; but Excel only wants to "Group" them; COUNT them, or SUM them. when All I want to do is drag them to separate columns and see them side by side. SideNote: PowerPivot will do this Easy.

1. So first I create a Pivot of my desired data, then I copy this data to a new sheet; then "Format Table As" creating a Named Table. then 'Fuzzy Lookup' to the original table. Adding all the "Text Columns" necessary.

Answer 2

You can use something called the Data Model to do this.

First, add a new Table to hold your comments:

Next, create a PivotTable out of the first Table, but be sure to check the option "Add this data to the Data Model".

You'll see that the PivotTable Fields box looks a little different than usual. Firstly, there's a little downward triangle by Table1, and if you click it, the various fields from Table1 expand/collapse.

And secondly, if you click on the text that say "All" you'll see that ALL Tables in your workbook are represented in the PivotTable Field dialog:

Now what we need to do is tell Excel that a relationship exists between these two tables. Click Data>Relationships from the ribbon:

...and select each Table from the drop-down on the left and the common field (in this case the Group field) from the drop-downs on the right:

Great: You're good to go. Now you can drag your fields of interest into your PivotTable, including the Comment field from that second table: