How to convert horizontal excel data to vertical excel data individually


I would like to know how to convert the raw data to a excel file
with the same company being together in the excel but different sheets

@JJ_Mystical

Welcome to community

Check below for your reference

Hope this helps you

Thanks

1 Like

Thanks for the solution,but i am new to ui path so i will try to replicate what ur solution is.Thanks!

1 Like

Hi.

You can just use a Read Range (check Add Headers), then use a 2 For Eaches: one for rows and one for columns - in order to transpose the data.

With the For Each row, you can assign a new file for each company name. With the For each column (regular ForEach), you can dynamically transpose the row.

You will also need a Build Data Table with blank data to add the transposed values to.

Here is an example using pseudocode:

Excel Read Range => companyData As DataTable

For Each row in companyData
    Build Data Table => companyDataNew, Have 2 columns
    Assign activity: companyFilePath = Path.Combine(companyDirectory, "company_"+row(0).ToString.Trim+".xlsx")

    For Each col in companyData.Columns.Cast(As DataColumn).ToArray => TypeArgument: DataColumn
        Add Data Row activity => companyDataNew, ArrayRow: {col.ColumnName, row(col.Column).ToString}

    Write Range activity => companyDataNew, companyFilePath

Sorry, If I made any mistakes.

Feel free to ask any further questions on any errors.

Regards.

1 Like