Hi I have a dataset in excel similar to the following :
| ID | Black | Red | Blue | Green | Orange | Yellow | Gray | Lilac | Magenta |
| 360 | Black | | | | | | | | |
| 873 | | | | | Orange | | | | |
| 095 | | Red | | | | | | | |
| 921 | | | | Green | | | | | |
| 084 | Black | | | | | | | | |
| 862 | | | | Green | | | | | |
| 874 | | | | Green | | | | | |
| 965 | | | | Green | | | | | |
| 668 | | | | | | | | Lilac | |
| 744 | | | | | | | | | |
| 124 | | | | | | | | Lilac | |
| 988 | | | | | | | | | Magenta |
What I need to do is 'transpose' this table so I have a two columns of 'ID' and 'ColorGroup' which results in :
| ID | ColorGroup |
| 360 | Black |
| 084 | Black |
| 095 | Red |
| 921 | Green |
| 862 | Green |
| 874 | Green |
| 965 | Green |
| 873 | Orange |
| 668 | Lilac |
| 124 | Lilac |
| 988 | Magenta |
Can anyone advise on the ideal solution for this?
Much appreciated!
