'Create table from combinations in excel
I'm looking for a way to combine these values into different columns in Excel: I have this table of users (in this example we only have 3 users, but could be up to 5 users):
| users |
|---|
| angel |
| luis |
| kevin |
What I need to do, is combine those names into 3 different columns (of course using an Excel formula or script), and at the end look like this:
| projects | oncall | operations |
|---|---|---|
| angel | kevin | luis |
| angel | luis | kevin |
| luis | angel | kevin |
| luis | kevin | angel |
| kevin | luis | angel |
| kevin | angel | luis |
| angel | kevin | luis |
| angel | luis | kevin |
| luis | angel | kevin |
| luis | kevin | angel |
| kevin | luis | angel |
| kevin | angel | luis |
| etc.. | etc.. | etc.. |
So, you can see that the users repeat the same amount of time in each column, example: look at the name "kevin", it appears 4 times in the column "operations", as well as luis and angel, the combination is really accurate.
The "projects" column must always repeat the same user 2 times, the "oncall" column must never repeat the user 2 times, and the operations column doesn't really matter, Here is another example, in this case with 4 users:
| users |
|---|
| kath |
| mary |
| yen |
| isa |
| projects | oncall | operations |
|---|---|---|
| kath | mary | yen & isa |
| kath | isa | mary & yen |
| mary | kath | yen & isa |
| mary | yen | kath & isa |
| yen | mary | kath & isa |
| yen | isa | kath & mary |
| isa | kath | mary & yen |
| isa | yen | kath & mary |
| kath | mary | yen & isa |
| kath | isa | mary & yen |
| mary | kath | yen & isa |
| mary | yen | kath & isa |
| yen | mary | kath & isa |
| yen | isa | kath & mary |
| isa | kath | mary & yen |
| isa | yen | kath & mary |
There can only be 1 user assigned to projects and to oncall, operations can have more than 1 user. Hope I made myself clear, any help is highly appreciated!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
