how to turn numeric / alphabetical field names into columns

Question

I have an excel file that shows me categories names, sales, datetimes and subcategory names in the format below, I'm trying to figure out how I can use Alteryx to re format this into 4 columns, one called "category" , one called subcategory, one called sales and one called datetime.

The number preceding the name value matches the category so 0 is linked to the food category and 1 is linked to the tools category. my ideal output would look like this with my four columns, another column is created for my subcategories, and I can use my category name column to group things together, and if the row is a category, then the category name is listed in the subcategory column instead of subcategory.

Does that make sense and is that possible?  and if so, how would I accomplish that?

I've attached a sample excel file.

test.xlsx

alexnajm · Answer

My updated workflow gets to the proposed solution!

AbdulBalogun · Answer

Hi @alexnajm , thanks for that! so, I tried that solution initially, but I ran into an issue, doing it that way may duplicate my values,

so for example, if I had 2 subcategories under food, my sales value of 11 would show up twice and every new column would have to be duplicated for subcategory. Do you think it's possible to accomplish using my desired output of 4 columns? that way even if I add new columns down the line, it won't blow up my data set.

alexnajm · Answer

This should get you started! The key is the Text to columns to split into a RecordID column of sorts and the header column, then Crosstabbing. If you need different split, I would look at getting the headers situated before the cross tab tool.

Edit: updated workflow!

how to turn numeric alphabetical field names into columns.yxzp

how to turn numeric alphabetical field names into columns.yxmd