How do I list multiple users who have the same role on one line in a table?

[rubenacampos]

I have user roles that have multiple users per account.  When I create a table to view the account details the table creates multiple lines per the account for the multiple users.  How would I list each distinct user on one line for each role.  I tried  the following without success:

=AGGR(CONCAT({<[User Role]={'Focal Point'}>}DISTINCT [User Name], ', '), [Contract ID])

I do not want  to use a pivot table b/c it's it doesn't look as clean as a table.  Thanks in advance!

[kenphamvn]

Hi

Try this 

=Aggr(Concat( distinct [User Name],','),[Contract ID],[User Role]) 
or 
=Aggr(Concat( {<[User Role]={'Focal Point'}>} distinct [User Name],','),[Contract ID],[User Role])

Regards

An Pham 

[rubenacampos]

Pham,

Using the 2nd formula I get the following result (see attached).  Not sure why there is an extra line created per contract ID.

The first formula also gives me multiple lines per contract ID.

Each contract ID has a Focal Point, Reviewing Official, and Approving Official roles but each role can have multiple user names.  Is it possible to have all the Focal Points for a Contract ID on one line (eg, Joe, Nick, John, etc..."

[jonathandienst · Accepted solution]

You dont need an Aggr() here. A simple Concat() with the correct dimensions should suffice:

CONCAT({<[User Role]={'Focal Point'}>} DISTINCT [User Name], ', ')

or add User Role to the dimensions and then

CONCAT(DISTINCT [User Name], ', '),