How to create a dual dynamic list of unique column values - including a short list of associated values per unique value?

twarner

I'm looking to create a 'dual list' of unique column value pairs. I can create a single list of unique column values with a helper Row # column and the following formula:

INDEX(DISTINCT(COLLECT({Epic ART Impacts: ART}, {Epic ART Impacts: Epic ID}, [Epic ID]#)), [Row #]@row))

When pointed at a list with MANY duplicates, this leads to the following list:

Row # ART

Row #	ART
1	ART 1234
2	ART 5678
3	ART XYZ

Now, I need to duplicate each one of those 'ART' names 4 times and add a pre-defined text value to a 3rd column. Each Distinct ART name needs to be copied to 4 rows with those 4 static Role names filled in a corresponding cell on the same row:

Row#	ART	Role
1	ART 1234	Role 1
2	ART 1234	Role 2
3	ART 1234	Role 3
4	ART 1234	Role 4
5	ART 5678	Role 1
6	ART 5678	Role 2
7	ART 5678	Role 3
8	ART 5678	Role 4
9	ART XYZ	Role 1
10	ART XYZ	Role 2
11	ART XYZ	Role 3
12	ART XYZ	Role 4

I am stumped on how to do this. If it cannot be done with standard Sheet formulas, I do have access to Bridge, but I'm not very advanced with that tool yet - just written a few simple workflows that work great, but aren't too complicated.

Any help is greatly appreciated.

Paul Newcome

Set up your roles in another table. The first column of the table would be numbers manually entered like so in a column called [Role Index]:

1

2

3

0

NOTE: Zero goes in place of four to make the final piece easier.

The next column over would be text number called [All Roles] and house the roles.

Role 1

Role 2

Role 3

Role 4

The [Art] column formula would change to:

=IFERROR(INDEX(DISTINCT(COLLECT({Epic ART Impacts: ART}, {Epic ART Impacts: Epic ID}, [Epic ID]#)), ROUNDUP([Row #]@row / 4)), "")

And the [Role] column formula would be:

=IF(Art@row <> "", INDEX([All Roles]:[All Roles], MATCH(MOD([Row #]@row, 4), [Role Index]:[Role Index], 0)))