Realignment for each row data

[SH_94]

Dear community,

I would like to ask how do we make the realignment for each row if we have the unorganized row data as below:

Many thanks for the help

[ShankerV]

Hi @SH_94 

You can achieve the solution with the help of macro.

You can pass each row one by one and use the Data Cleansing tool to remove the columns which are null().

Then create the desired output.

Many thanks

Shanker V

[SH_94]

Hi @ShankerV ,

Can you share sample macro for this?

thank you

[DavidSkaife]

Hi @SH_94 

Here you go;

Note: as there is no data for Location 4 or 5 it won't ouput those headers as its set to auto configure the output by position

[Ben_H · Accepted solution]

Hi @SH_94,

Here's an alternate method that doesn't require a macro.

The input is configured to have first row contains data so that the headers are defaulted to f1, f2 etc.

Add a record Id, transpose the data and remove nulls. I've then used the tile tool to add a column sequence and crosstab back into the right format.

Dynamic rename corrects the headers.

Note that this method will only work where data is not missing values, i.e. if on a single row the 3rd column was blank but 4 and 5 were not you'd get records in the wrong order.

Regards,

Ben