Data arrangements

[Mostafa_Anwar]

Dear All,

In the attached excel file you will find two tabs (Current Data & Looks Like) Just i want to know how to make workflow to move the data from different columns to designated column and this column representing the filled data.

Thanks & Regards.

[JohnJPS · Accepted solution]

Hi @Mostafa_Anwar,

I got it as far as ...

... which is pretty straightforward with Transpose and CrossTab with a little twiddling in between.

Perhaps the techniques utilized can get you a step closer to your ultimate goal.

I thought it looked pretty clean as is, though, and perhaps various discrepancies are sample-data-based and not approach-based?

Hope that helps!

John

[Mostafa_Anwar]

Thanks @JohnJPS  for your respond, Your proposal is awesome, However i want to show the status column as well even if there null rows will be displayed,

Attached is the excel file that i want to display the data as is so appreciate your support here. 

[JohnJPS · Accepted solution]

Hi @Mostafa_Anwar,

Essentially the missing columns are just labels, so you can add them back in with a Formula tool that basically hard-codes them.  This has been done in the attached modification here.

[Mostafa_Anwar]

Hi @JohnJPS 

Thanks for your respond its awesome! Just i have noticed that the highlighted cells in the attached pic shows YES,NO in the same cell, How to split the value either YES or NO in each cell such as PO status or Approved 1 columns.

Regards,

[JohnJPS]

I can think of 3 ways to approach that.  If you want just a YES or NO, I'm not sure how you would decide which...

• You could adjust the CrossTab to use either First or Last rather than Concatenate.
• You could add a MultiField Formula at the end of everything and manually hard-code a handler for either YES,NO or NO,YES values, (e.g. if YES exists use YES, or if NO exists use NO... that sort of logic)
• You could use Text to Columns tools to split and have two columns, one for YES and another for NO (though, I wouldn't think that would make much sense.

I'm not sure what your application is, but either of the first two bullets seem like they could make sense.