Excel reformat/transpose bulk records

Copper Contributor

Hello,

i am trying to find an automated way to reformat data in a (csv) spreadsheet to a new format that will be accepted as an import into another non office application.

The problem i have is that there just over a thousand records to reformat so manually would take a long time and risk human error.

 

The current format is as follows:

 data_1data_2data_3data_4data_5data_6data_7data_8
62000team1team1     external
62001team2team2     external
62002team3team3     external

 

i need to reformat to this:

62000data_1team1
62000data_2team1
62000data_3 
62000data_4 
62000data_5 
62000data_6 
62000data_7 
62000data_8external
62001data_1team2
62001data_2team2
62001data_3 
62001data_4 
62001data_5 
62001data_6 
62001data_7 
62001data_8external
62002data_1team3
62002data_2team3
62002data_3 
62002data_4 
62002data_5 
62002data_6 
62002data_7 
62002data_8external

 

is there a way to do this? i tried macro recording but the code was complicated to repeat for every record.

there might be data in the fields which are blank.

thanks,

1 Reply

@ferrariwa100 Looks like you need to look into PowerQuery and "unpivot".

Chapter 13 in the attached link tells you all about it. but follow through the "program" from the beginning if you are not familiar with PQ

https://exceloffthegrid.com/power-query-introduction/