I have a data set that I need to have transposed, but the data is grouped. I need to be able to cross N rows at a time. I was thinking of using the sample tool, but sometimes in my actual data set the values below the berries are all the same. Here is an example of the data that I have currently: Current Column Strawberry123Blueberry122Raspberry111 And here is how I want my data to look after the cross tab: Current ColumnF2F3F4Strawberry123Blueberry122Raspberry111 Is there any combination of tools that will get these results for me? Thank you!

Grouped Cross Tab

I have a data set that I need to have transposed, but the data is grouped. I need to be able to cross N rows at a time. I was thinking of using the sample tool, but sometimes in my actual data set the values below the berries are all the same.

Here is an example of the data that I have currently:

Current Column
Strawberry
1
2
3
Blueberry
1
2
2
Raspberry
1
1
1

And here is how I want my data to look after the cross tab:

Current Column	F2	F3	F4
Strawberry	1	2	3
Blueberry	1	2	2
Raspberry	1	1	1

Is there any combination of tools that will get these results for me?

Thank you!

Transformation

Accepted answers

estherb47

Hi @Snord

You can use Make Columns to give you your exact result.

It's in the Laboratory tool category

Let me know if that helps!

Cheers!

Esther

All comments

estherb47

Hi @Snord

You can use Make Columns to give you your exact result.

It's in the Laboratory tool category

Let me know if that helps!

Cheers!

Esther

NickSm

Hey @Snord

Make columns as above is actually probably the easiest way to do so if you know how many records of numerical data there are for each.

Also worth noting that RegEx could be an easy way to break that column out for an easy pivot. Can put words into one column and digits into another, then fill down and flip.

If there's a scenario where you might have words and digits in one entry you may have to get a little more specific.

GroupPivot.yxmd

JosephSerpis

Hi @Snord I mocked up a workflow let me know what you think?

Crosstab170919.yxmd

Snord

Esther, I hadn't ever used this tool. This is brilliant. Thank you so much!

Snord

@JS420, thank you for the help. This is a great way to use the cross tab tool, but I think it is overkill for the little process I have going on. Thanks for the suggestion all the same!

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335