How to Transpose Excel data or Data Table?

News Tutorials
, , , , ,
1

Hello UiPathans :robot:

Here we discuss how to Transpose the data in Excel or a data table

What is meant by Transpose?

To change something from one position to another
In Excel Change the ROW as a column, COLUMN as a Row

Input
image
Output :point_down:
image

  • Transpose can achieve by two methods
    ā†’ Modern activity- Using Copy/Paste range Activity.(For Excel Processing)
    ā†’ Novigo Solutions. (For Datatable Processing)

Modern activity- Copy/Paste range Activity.

  • By Using modern activity we can achieve the transpose method.
  • For classic users can select Show Modern from the Activity panel, now modern activities can use by classic users.
    image

Steps

  1. USe Excel process scope and place the Use Excel Activity
Visual

image

  1. In the use of excel activity select the path of the file required to transpose the data.

  2. Drag and drop the Copy/Paste Range activity

  3. In the source we need to provide the data need to transpose and the destination is to action which paste the transposed data.

Excel.Sheet("Sheet1")
Where Excel is a variable of Reference as set on the Use Excel File
Sheet("Sheetname") in which need to mention the Sheet name, Same for destination

  1. Can also select what properties need to copy along with it.

    • Values
    • Formats
    • Formulas
      image

  2. It is mandatory to check or click the transpose option on Activity, Otherwise, just record copy and paste without transposing

Visual

image

Novigo Solutions

Steps

  1. Need to install the activity from the manage packages
  2. Search NovigoSolutions.TransposeDataTable.Activities from All packages and install, and save the activity. Refer to the screenshots by clicking Visual :point_down:
Visual

image
image903Ɨ509 25.7 KB

image
image

  1. Read the Excel file need to transpose and save the output as a Data Table Variable
  2. Drag and drop the Transpose Datatable activity by Novigo.
  3. Pass the variable of Read Data Table in it. and can give the same Variable for output or Different variables also can pass in it.
  4. Using the write range activity we can write the transposed data into different sheets or different files.

For more reference, attached XAML file here :point_down:
Tutorial_Transpose.zip (104.0 KB)

Happy Learning
Happy Automation :wink:

Regards,
Gokul Jai

3 Likes
2

Also this can be done using a VBA (Macro)
Thank you

3

How to do it??

4

NovigoSolutions - TransposeDataTable doesnt support the latest studio version. is there any other alternatives?

5

Hi @Aisya_Yahaya

You can follow the below video steps

Regards