Excel reformat/transpose bulk records

Question

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.&nbsp;The current format is as follows:&nbsp;data_1data_2data_3data_4data_5data_6data_7data_862000team1team1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;external62001team2team2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;external62002team3team3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;external&nbsp;i need to reformat to this:62000data_1team162000data_2team162000data_3&nbsp;62000data_4&nbsp;62000data_5&nbsp;62000data_6&nbsp;62000data_7&nbsp;62000data_8external62001data_1team262001data_2team262001data_3&nbsp;62001data_4&nbsp;62001data_5&nbsp;62001data_6&nbsp;62001data_7&nbsp;62001data_8external62002data_1team362002data_2team362002data_3&nbsp;62002data_4&nbsp;62002data_5&nbsp;62002data_6&nbsp;62002data_7&nbsp;62002data_8external&nbsp;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,

riny_van_eekelen · Answer

ferrariwa100&nbsp;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 PQhttps://exceloffthegrid.com/power-query-introduction/&nbsp;

Forum Discussion

Excel reformat/transpose bulk records

1 Reply

Resources

	data_1	data_2	data_3	data_4	data_5	data_6	data_7	data_8
62000	team1	team1						external
62001	team2	team2						external
62002	team3	team3						external