Macros Use

Question

hello. i need help i have a large data with 3 or 5 separate items ,lumped up on reach row .i have use text to columns to separate the 3 or 5 items to separate columns. i need a macros that can now allow these separated columns align.

i have attached a file with the data.

Please help!!

logaraj sekar · Answer

Hi&nbsp;iub_ek&nbsp;The data and your explanation is not sufficient to answer.&nbsp;You did not specified how you separate these columns.

iub_ek · Answer

hi. Apologies on that i used Text to columns to separate the items lumped together so they are on different columns. However i then need to know how the separated items can be on different rows below the initial column.i.e Consultaion,fbs,fbs, then becomes

Consultaion

fbs

Fbc

logaraj sekar · Answer

Hi&nbsp;iub_ek&nbsp;Still, i can't able to get you.&nbsp;&nbsp;Explain what is&nbsp;Consultaion,fbs,fbs and where is it in excel?&nbsp;

iub_ek · Answer

hello. thought you were trying to help me out.

sergeibaklan · Answer

Hi iub_ek,
&nbsp;
Than could be done with Power Query. Straightforward variant
- load raw data to query,
- split "servicerendered" on columns by comma delimiter;
- unpivot generated new columns;
- remove attribute column;
- land the table back to Excel sheet
&nbsp;
Please see in Sheet1 attached.
To make above bit more flexible simple code is added in second query to define max number of separators and based on it to generate list of columns on which to split. Rest is the same. Exactly the same result in Sheet2 in attached.

Forum Discussion

Macros Use

Share

Resources