Forum Discussion
Transform excel data with merged headers
- 4 years ago
After many hours paying around, I found the solution... I'm posting it here so it can help someone else... It is somehow related to the amitchandak document (thanks for that dude!).
Starting with:First of all, I deleted the first row with the name of the employees, and ser up the new first row as headers. That gives this:
Then, I selected "Fecha", and Unpivoted all the other columns, resulting in this:Then the tricky one... what I need to pivot and get the result I want, is to organize the columns in a way that I have the date in the first row, then a column with the number of the employee that each rows refers to, then a column with the headers of the rows I want in my final table, and then the values. I managed to get that creating a new column that extract the number on the attribute column (that is the number of employee (1, 2, 3 etc). And then creating another column that extracts the first 3 characters of the atribute column (that will be the headers of the new columns when I pivot). Reordering that, and deleting the original attribute column, I got this:
Finally, I pivoted the columns selecting "First characters" and "Value" columns, to get this:
And that is exactly what I wanted!
After many hours paying around, I found the solution... I'm posting it here so it can help someone else... It is somehow related to the amitchandak document (thanks for that dude!).
Starting with:
First of all, I deleted the first row with the name of the employees, and ser up the new first row as headers. That gives this:
Then, I selected "Fecha", and Unpivoted all the other columns, resulting in this:
Then the tricky one... what I need to pivot and get the result I want, is to organize the columns in a way that I have the date in the first row, then a column with the number of the employee that each rows refers to, then a column with the headers of the rows I want in my final table, and then the values. I managed to get that creating a new column that extract the number on the attribute column (that is the number of employee (1, 2, 3 etc). And then creating another column that extracts the first 3 characters of the atribute column (that will be the headers of the new columns when I pivot). Reordering that, and deleting the original attribute column, I got this:
Finally, I pivoted the columns selecting "First characters" and "Value" columns, to get this:
And that is exactly what I wanted!