Need help creating matrix like this.

Question

In this attachment below, I have a large joined file in the current file format and we wanted to create a matrix format in the goal session. Right now I am manually adding all the prices together to create this matrix in the attachment below. I would greatly appreciate it, if someone can help me create a sample workflow for this.

Thank you!

Help please.xlsx

Peachyco · Answer

@mst3k's solution works. If you'd like to clean up the column names, you can add a "Dynamic Rename" tool in the end. You can use the REGEX_Replace function to replace the underscores (_) with spaces. 😊

mst3k · Answer

just need to use the crosstab tool, which has a built-in sum formula. now, you also want to add together the price1 and price2 in your row, because crosstab will only sum up a single column and you're essentially trying to sum together 2 columns.

the multifield tool just converts all the nulls to 0 to match your desired output

Crosstab.yxmd