Aug 29 2022 03:53 AM
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_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 |
i need to reformat to this:
62000 | data_1 | team1 |
62000 | data_2 | team1 |
62000 | data_3 | |
62000 | data_4 | |
62000 | data_5 | |
62000 | data_6 | |
62000 | data_7 | |
62000 | data_8 | external |
62001 | data_1 | team2 |
62001 | data_2 | team2 |
62001 | data_3 | |
62001 | data_4 | |
62001 | data_5 | |
62001 | data_6 | |
62001 | data_7 | |
62001 | data_8 | external |
62002 | data_1 | team3 |
62002 | data_2 | team3 |
62002 | data_3 | |
62002 | data_4 | |
62002 | data_5 | |
62002 | data_6 | |
62002 | data_7 | |
62002 | data_8 | external |
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,
Aug 29 2022 11:12 AM
@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