I have data that currently looks like this, with the first row being the headers:
Organization | Teams | North America | Asia | ||||||
Total | Male | Female | Unknown | Total | Male | Female | Unknown | ||
Organization 1 | Red Team Total | 9 | 5 | 3 | 1 | 9 | 1 | 6 | 2 |
Organization 1 | Red Team 1 | 5 | 2 | 3 | 0 | 4 | 1 | 1 | 2 |
Organization 1 | Red Team 2 | 4 | 3 | 0 | 1 | 5 | 0 | 5 | 0 |
Organization 1 | Blue Team Total | 1 | 1 | 0 | 0 | 8 | 2 | 6 | 0 |
Organization 1 | Blue Team 1 | 1 | 1 | 0 | 0 | 8 | 2 | 6 | 0 |
Organization 2 | Red Team Total | 2 | 0 | 1 | 1 | 5 | 1 | 4 | 0 |
Organization 2 | Red Team 1 | 2 | 0 | 1 | 1 | 5 | 1 | 4 | 0 |
I am trying to get it into the following format to help with dashboarding (if there is a better format than this, let me know):
Organization | Teams | Location | Gender | Value |
Organization 1 | Red Team Total | North America | Male | 5 |
Organization 1 | Red Team 1 | Asia | Female | 1 |
Organization 2 | Red Team 1 | North America | Unknown | 1 |
Kindly asking for help as I tried using the Transpose tool but unsure of the exact steps to perform this transformation.
Thanks in advance!
Solved! Go to Solution.
Not sure if this is dynamic enough for you, but check out a few of the reformatting methods used, they may help.
Thanks for your help! This works!