Wasn't sure how to title this, but I have data in multiple columns after doing a Text-to-column process. I need the first few columns (fields) copied to a new row for each one of the newly created columns from the text-to-column process.

Example below:

Original Data

      A      B         C           D          E         F         G
1  Name1   City1   Nickname1   Address1   Address2  Address3  Address4 ...
2  Name2   City2   Nickname2   Address1   Address2  ...

What I want:

     A       B         C           D  
1  Name1   City1   Nickname1   Address1
2  Name1   City1   Nickname1   Address2
3  Name1   City1   Nickname1   Address3
4  Name1   City1   Nickname1   Address4
5  Name2   City2   Nickname2   Address1
6  Name2   City2   Nickname2   Address2

Image instead of text since text isn't always pretty: Image of example instead of text

  • You need to unpivot / reverse pivot your data, there are several tips for that already on the Internet. – Máté Juhász Aug 3 '16 at 19:03
  • I have found examples of how to get data from columns D,E,F,G... into a single column, but not how to create a routine or loop to copy the data in columns A,B & C into each of the new rows and only for when there is something in one of the D+ columns. – Dawgfather Aug 3 '16 at 20:01
  • Please share what've you tried and what exactly doesn't work. You're supposed to do research for your question and ask specific questions instead of just asking for entire solutions. – Máté Juhász Aug 3 '16 at 20:04
  • If you have Excel 2010 or later, use Power Pivot or Get & Transform to unpivot the columns you wish duplicated. – Ron Rosenfeld Aug 3 '16 at 21:33

Browse other questions tagged or ask your own question.