Filling the same data for many rows with minor addition

Question

I have some data as follows:

         Organization Name       Mail        Role1  Role 2 Role3 
         XYZ          Kumar      kumar@.com  A      B      C

which I need separate in rows for each role as shown, so my requirement is:

       Organization  Name       Mail        Roles
       XYZ           Kumar      kumar@.com  A
       XYZ           Kumar      kumar@.com  B
       XYZ           Kumar      kumar@.com  C

Is there any method to make it done?

What have you tried and what direction are you wanting to go? Do you want a formula or VBA? Please elaborate. — tbm0115, Oct 15 '15 at 17:24

score 0 · Answer 1 · edited May 23 '17 at 12:14

I'm guessing you have many rows of organizations and name. Assuming Oganization is in A1, insert a new ColumnD and populate it to suit with:

  =A1&"|"&B1&"|"&C1

To the populated range of ColumnsD:G apply the unpivot process as described in detail here. Having created the Table insert new ColumsB:C, delete ColumnD and apply Text To Columns to ColumnA, with | as the delimiter. Adjust the labels to suit.

Filling the same data for many rows with minor addition

1 Answers1