Forum Discussion

iub_ek's avatar
iub_ek
Copper Contributor
May 28, 2018

Macros Use

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 a...
Macros & VBA
Show and Tell
Logaraj Sekar's avatar
Logaraj Sekar
Steel Contributor
May 28, 2018

Hi iub_ek

 

The data and your explanation is not sufficient to answer.

 

You did not specified how you separate these columns.

iub_ek's avatar
iub_ek
Copper Contributor
May 28, 2018

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's avatar
    Logaraj Sekar
    Steel Contributor
    May 28, 2018

    Hi iub_ek

     

    Still, i can't able to get you. 

     

    Explain what is Consultaion,fbs,fbs and where is it in excel?

     

    • iub_ek's avatar
      iub_ek
      Copper Contributor
      May 31, 2018

      hello. thought you were trying to help me out.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 31, 2018

        Hi iub_ek,

         

        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

         

        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.