Crosstable / Transpose Help

[fiorano]

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!

[AmeliaG · Accepted solution]

Hi @fiorano,

Thanks for your question! I've attached a workflow which uses transpose in conjunction with a filter to achieve your desired results. 

Let me know if you have any questions!

Amelia

[fiorano]

Perfect thanks !!!!