Transforming Column to Rows

[KamenRider]

Hi,

I have here columns by which I would like to be transform into rows. By transforming to rows, I only needed Nick name, Letter and price. The sample below on the left side shows the raw data and the right side is the results or the output.

 

 

I have a hard time converting it for it is very complicated to me. I hope someone will help me on how to do this. The final price is computed by J+M/32.

Looking forward to share your expertise on this.

Kamen

[CoG]

Here is the beginning of the workflow to accomplish what you requested. The rest should be quite simple to add, according to your needs.

Best wishes!

[KamenRider]

Hi @CoG 

Thanks for helping me this out. I have run your workflow and I found an error. The formula seems right to me however can't figure out what is wrong with it. Can you also check it?

 

Thanks,

Kamen

[CoG]

I am not getting an error on my end at all. I'm curious if that's a bug, because that should not cause any issues. Here is a work-a-round that will hopefully work for you.

[KamenRider]

Hi @CoG 

Thanks, your 2nd workflow works. I have another problem by which I am a kind of confuse since I follow your workflow and yet I get different results. I am not quite sure which part do I get it wrong. Please help me on this. Below is the results.

 

Looking forward for your response. Thanks.

Kamen

[CoG]

What do your Multi-Row settings look like? I do not know why you are getting that particular issue exactly, but the problem is that the Letter and Price rows before the Cross-Tab tool, are not being given the same RecordID so that they rotate into the same row. I know that may not be super clear reading it so here is a simple example of what the data should look like before and after the cross-tab tool:

RecordID	Nick Name	Name	Value
1	A	Letter	B
1	A	Price	99-15

Instead what you have looks more like this:

RecordID	Nick Name	Name	Value
1	A	Letter	B
2	A	Price	99-15

The multi-row is what is supposed to change the RecordID to the proper form, so that's where I would look first.

[KamenRider]

Hi @CoG 

I think you are right. Is there a solution to how my data are arrange?

 

Please advise.

Kamen

[CoG · Accepted solution]

I knew it!!! In the Group By box, you need to check [RecordID Parity] as well (at the bottom of the list). You need to group on both Nick Name and Parity.

I feel pretty good about this solving it. Let me know if it works.

Happy Solving!!!

[KamenRider]

Hi @CoG 

You nailed it! Thank you so much for your expertise.

Kamen