Excel Transpose row to column

Help Community
,
1

hi, want to transpose row to column data in excel.
without using linq query, any shortcut keys or macro.
is it possible through Data table activities and looping if.

Note: Rows and columns are dynamic.

2

Hi,

You can follow below steps:

  • Excel Application Scope (Open Excel File)

    • Read Range (Read Data into originalDataTable)

  • Build Data Table (Create transposedDataTable with desired column structure)

  • For Each Row in originalDataTable

    • For Each Column in originalDataTable
      • Add Value to transposedDataTable

  • Excel Application Scope (Open/Select Excel File to Write Transposed Data)

    • Write Range (Write Transposed Data to Excel File)
    • Close Workbook (Close Excel File)
3

Hi @suraj.l ,
Have many way to Transpose row to column in excel
1.use for loop, we have dtinput and dtoutput
each row of dtinput, use add data columns to dtoutput

2.use invoke vb code

3.use hot key when open excel file

regards,

4

Hi @suraj.l

Please go through this video

Hope this helps!!

5

Hi @suraj.l

To transpose row data to column data in Excel without using LINQ, shortcut keys, or macros, you can use UiPath’s DataTable activities and a loop. Here’s a high-level overview of how you can achieve this:

  1. Use the “Read Range” activity to read the data from the Excel file into a DataTable.
  2. Create a new DataTable to store the transposed data. The number of rows and columns in this new DataTable will be the opposite of the original DataTable.
  3. Use a loop (e.g., For Each Row activity) to iterate through the rows of the original DataTable. For each row, iterate through the columns, and add the cell values to the new DataTable in a transposed manner.

This workflow will transpose the data from rows to columns. It assumes that the number of rows in the original DataTable will become the number of columns in the transposed DataTable and vice versa. You may need to adapt it to your specific Excel data structure.

Thanks!!

6

Cross reference:

7

@Nguyen_Van_Luong1
as i mention don’t want any scripting code or shortcut keys read once my question properly

8

@ppr please provide me xaml regarding this if possible.

9

hi @suraj.l ,
in 3 ways, you can select 1
use activity
for loop
each row original data, add data columns to target data
or
LINQ
regards,

10

@Nitya1 please provide xaml file as i mention the logic which you are saying here rows are dynamic in rows and columns as i noted in question please read once and share xaml file.

11

@Irtetala
Please read my question properly once i need to transpose from row to column where rows and columns both are dynamic.
provide xaml for same.

12

provide me the looping way through datatable activities share xaml for same.

13

Can you share sample file?
my output

image
image701Ă—373 12.8 KB

my input
image
image229Ă—512 7.16 KB

my step
image
image530Ă—728 17 KB

only activity

14

Any blockers to complete it along with the given detailed explanations? What was done so far at your end?

15

row to column.xlsx (9.5 KB)
where rows and column are unstructure and dynamic.

16

row to column.xlsx (9.5 KB)

where rows and column are unstructure and dynamic.

17

will match the explanation, what we gave for the case

Show us your XAML and what was modelled so far at your end. Thanks

18

bro why do you sound so frustrated and rude at the same time in the reply section, everyone above tried to help you. Be Grateful bro

2 Likes