Excel Data clean up - Transpose the years to single column

[c20091929]

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. 

[DawnDuong]

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 

[KarolinaRoza]

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