How to convert horizontal excel data to vertical excel data individually

[JJ_Mystical (Jun JIe)]

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

[Srini84 (Srinivas Kadamati)]

@JJ_Mystical

Welcome to community

Check below for your reference

Horizotal data in EXCEL to Vertical data

I’m assuming the Page1 Excel has all the information in the first column. So first thing is you need to know what keywords you are looking for to extract. In this case, you can use basically your column headers that you want. Let us actually store the keywords into a new Data Table with Build Data Table (if you don’t already have an existing spreadsheet. Then, we can use those column headers as your key words. Build Data Table // for example I'll use table called dtNew Then, let’s Read Range …

Hope this helps you

Thanks

[JJ_Mystical (Jun JIe)]

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

[ClaytonM]

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.