Merging multiple rows

Hi,

Here is my read range (Input sheet):

The Output I need:

Output

Basically in need all the names in one row and all the roll Numbers in another row in an new sheet

Number of rows and columns (in Input sheet) are dynamic and not fixed.

Thanks a lot :slight_smile:

@Bhanu_Rathore

All 3 sets of data will be in single sheet or multiple sheets ?

All 3 data merged in a new single sheet.

Image 1 is my Input sheet.

I Need output as image 2

@Bhanu_Rathore

Try below steps.

  1. Use Read Range activity and specify range as “”. It will give output as DataTable and let’s say ‘InputDT’.

  2. And then find total number of rows in the input file as below.

             Int rowCount = InputDT.Rows.Count
    
  3. And then use 3 Read Range activities to read each table and specify range as below.

           "A1:C"+rowCount.ToString
           "E1:G"+rowCount.ToString
           "I1:K"+rowCount.ToString
    
  4. And then use Merge DataTable activities to merge one table to other.

  5. And then finally use Write Range activity to write into Excel file.

Hi,

Can you try the following sample?
This sample merges each column which starts with “Name” and “Roll No” dynamically.
(Assumed number of Name columns is same as Roll No)

dtResult = dt.AsEnumerable.SelectMany(Function(r) dt.Columns.Cast(Of DataColumn).Where(Function(c) c.ColumnName.StartsWith("Name")).Zip(dt.Columns.Cast(Of DataColumn).Where(Function(c2) c2.ColumnName.StartsWith("Roll No")), Function(x,y) dtResult.LoadDataRow({r(x),r(y)},False))).CopyToDataTable

Sample20210925-5.zip (7.1 KB)

Regards,

2 Likes

Hi @Bhanu_Rathore ,

In addition to solutions provided by others, I have attached my flow. It does following.

  1. Read data from excel.
  2. Retrieve individual data tables from it.
  3. Merge all individual data tables.
  4. Remove empty rows from merged data table. This step is applicable incase one individual data table is having more rows then others.
    MergeData.zip (11.7 KB)

Thanks,
Kapil

Hi @lakshman

Range is dynamic

@Bhanu_Rathore

The above mentioned steps should work if range is dynamic.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.