Excel Data clean up - Transpose the years to single column

Question

Hi guys,

I hope everything is going well with you.

I need your help with this question.

I am working on the attached excel file, which contained Country, Subject Descriptor, Subject Notes, Unit, Scale, and different years as the separate column.

I am trying to transpose the years to the below sample (better for our database)

I was trying to use Arrange function, but it took so long to add all the years to the column.

I know we can use the Multi-Row formula to achieve the same result.

Please let me know how I should use this...

Thank you so much for your time.

1.xlsx

KarolinaRoza · Answer

hi @c20091929 ,

I am fully sure what you want to achieve, but when it comes to transpose you can use Transpose tool.

I prepared simple workflow for you.

I think you excluded years with n/a value but using Transpose Tool you can keep all years in one column.

regards,

Karolina

transpose1.yxmd

DawnDuong · Answer

Hi @c20091929

Actually you can use the Transpose Tool to achieve the exact same result.

Select all the “years” columns as Data fields to transpose and the rest of the columns as key fields

if you have not done so, i recommend u explore the interactive lessons under the group of “for excel users” - very useful if most of your work involves manipulating excel data into more tractable formats.

Dawn