Merge multiple rows in one row on basis of rule

Following is the csv file that I am getting. Now I want to merge multiple rows on the basis if col6, col7 and col8 are empty then merge the rows with the above one.
Input csv:

Output that I expect is:

Thanks in advance!


Please follow the steps

  1. Create 2 counter variablesr with int32 type and initial value of counter1 is 0 and counter2 is -1
  2. Read the data from excel to datatable . Check the add headers check box(dt)
  3. Use a for each loop (not for eqch row in datatable) give the In argument value as Enumerable.Range(0,dt.rowcount).ToArray and change type argument to int32
  4. Now inside for loop use if condition with String.IsNullOrEmpty(dt.rows(counter1)(5).ToString) andAlso String.IsNullOrEmpty(dt.rows(counter1)(6).ToString) andAlso String.IsNullOrEmpty(dt.rows(counter1)(7).ToString)
  5. In the then side use assign
    And dt.rows(counter2)(0) = dt. rows(counter2)(0).Tostring + " "+ rows(counter1)(0).ToString. Do this for each column by changing 0 to 1 then 2 and so on in multiassign.
  6. In the same then block use a remove datarow and pass counter1 as the row number
  7. In the else block use assign with counter2= counter2+1 and also counter1=counter1+1

At the end of for loop use write range to write the data back to excel


Hi @Umer_Shahid ,
Please refer below xaml file to achive your logic
New Microsoft Excel Worksheet.xlsx (10.3 KB)
Sequence.xaml (23.3 KB)



@arivu96 thank youuuu so much. Your solution worked!!

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