Splitting data to multiple columns

Question

I have a column All_ID's which has all id's added within that column using ; as a delimiter. The id's are listed as "243567679;A;46776757;B;e63428534;C" where "243567679" is A id, "46776757" is B id and etc. My goal is to split this data properly so that all A id's goes under A column, B id's goes under B column and C id's goes under C column. I split this data using Text to Column tool but I'm having an issue where Id's are getting mixed up when sometimes A, B or C id's are in different order or one of them missing.

I really need Alteryx community advice of this. Sample data attached.

Thank you in advance.

Sample Made up data.xlsx

Aaron_Harter · Accepted Answer

Hi @James01,

If you parse the data set to rows, then use the Multi-Row formula tool to identify the corresponding column name, you can then Cross Tab into the desired data table shape: