Need to convert my input file into a specific format

Hello Everyone,

I have a input file which looks like this,

And I want to convert each row into a specific format, the example for the output format is like this,

Note:- This is just an example where I have manually converted 1st row of the input file to get the result

What I want is to create a block like above for all the 16 entries present in the input file so in the output file there should be 16 blocks one below the other. I have tried various methods but I'm not able to get the desired output.

And thus I'm back posting in this awesome community where I have found answers to many of my problems.
Any help would be highly appreciated.

I'm attaching the input and desired output file for convenience.

Input.xlsx

Output.xlsx

Workflow

Reporting

Transformation

Preparation

Iterative Macro

Accepted answers

pdave87

@swarajjoshi *****Abracadabra****** Your wish is granted 🙂 Happy Solving!!

V2_Data_Transformation_Requirement.yxmd

All comments

pdave87

@swarajjoshi Hi, did you tried transposing data?

swarajjoshi

Hello @pdave87 ,

Thanks for your response.

But I have tried transposing it and that works fine for the first part of the output block but if you see there is a row below the first 5 lines in my output file which contain the Item Code, Lot details, Lot locations & Qty(EA) and that I see is missing from the solution you have shared.

So you can try and add that part too

pdave87

here we go, Alteryx has all the tricks to provide desired output (magic) 🙂 Workflow attached for your test run!

Transpose_Union_Solution.yxmd

swarajjoshi

Hey @pdave87 ,

You're pretty quick human and almost there as you see the solution provided by you is almost what I wanted but the latest row you added doesn't contain the required headers, here's is what I want the latest added rows to look like

You have got the data, just the headers are missing

pdave87

@swarajjoshi Here we go again! happy solving!! 🙂 Tested Workflow attached for reference.

Transpose_Union_Solution.yxmd

pdave87

@swarajjoshi For better representation, refer attached workflow. It includes null rows for each stack of data 🙂

Data_Transformation_Requirement.yxmd

ChrisTX

The attached workflow seems to give the output format you're looking for.

The key is to use the Union tool to manually align the columns.

Chris

Need to convert my input file into a specific format.yxmd

swarajjoshi

@pdave87 @ChrisTX

Thanks so much for putting in the efforts but there is a slight change in the kind of output that I need,

now for example if the Market from & Market To are same the lots should be place one below the other and no different bloc should be made.

For e.g. If Market from if 'Switzerland' and Market To is 'United Kingdom' the output should look like,

I'm attaching the output file for reference.

And sorry for the last minute changes as I know you guys had spent your time for providing me the output.

Book1.xlsx

pdave87

@swarajjoshi *****Abracadabra****** Your wish is granted 🙂 Happy Solving!!

V2_Data_Transformation_Requirement.yxmd

swarajjoshi

Hey @pdave87

Thank you soo much man, it was a great great help.

Cheers, bye

pdave87

@swarajjoshi We are happy to help and learning remains to be a continuous process because of this fabulous community.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335