How to split rows/columns based then union/append them again?

[JoyHe]

I need to deal with a file like below, to do further transform, I need to make all questions in one row。

From:

To:

I am not sure if it is possible, and I have tried another way, transform the file first, but I still need to split columns then append, like these:

From：

To：

 Any one can help me to achieve my goal, please provide me some ideas.

BTW, since the real file is much more complex, there may be many questions and the answers can be in different format, I cannot use summary-concatenate-text to column to achieve my goal.

Really appreciate.

Joy

[atcodedog05]

Hi @JoyHe 

Here are some workflows which might give you some idea.

Hope this helps 🙂

[shreyanshrathod · Accepted solution]

@JoyHe ,

See if this helps I have built an Iterative macro that is completely dynamic

This is the input that I have considered :-

A screenshot of the macro :-

and the results :-

I am attaching the workflow for your reference.

Let me know if this resolves your query.

Regards,

Shreyansh Rathod

[shreyanshrathod]

Also, here I have assumed that there will be similar number of IDs ( 1 and 2 in my example).

If the number of IDs vary for each quarter, consider substituting the Macro output tool in the macro with an Excel / csv file.

This is because the Macro output only unions columns with similar structure.

Regards,

Shreyansh Rathod

[JoyHe]

thank you @atcodedog05 you really give me different possible ways. The real file is much more complex and I am trying to make the use of your suggest

[JoyHe]

@shreyanshratho  Thank you very much! I am trying to use it my workflow!

[shreyanshrathod]

@JoyHe ,

The configuration of the tools within the macro, .i.e. Dynamic Rename, Dynamic Select, Transpose and CrossTab will change according to columns in your original data. I built this macro with my assumed input.

Regards,

Shreyansh Rathod

[JoyHe]

@shreyanshrathod

I saw it and I have re-created my own templet, I think I will resolve my question soon, thanks again!

[shreyanshrathod]

@JoyHe ,

Glad that I could help.

It was an interesting use case.

If it satisfies your query, kindly mark it as Solved.

Regards,

Shreyansh Rathod