How to insert category wise blank row in Excel Sheet?

Existing workflow, I already develop insert blank row activity in the Excel sheet but it’s work fixed after every 02 rows.

But my expectation is, How to insert blank row dynamically in the excel sheet category wise.

image

This link may help you,

2 Likes

I want to category wise that column name is “Service Item Group Code” separate using blank row… see my excel example

So you want to add a empty cell below a particular column???..

2 Likes

yeah right @Manish540

Example, IBM X row count is 4, so after 4 rows add a blank row, then next is “IBM SW” that count is 01 so after 01 row insert a new blank row etc.

Have any idea about this task…

Cheers @lakshman

1 Like

@ARahman

Just give it a try like this.

  1. First use Read Range activity to read data from Excel file and will give you output as DataTable and say ‘inputDT’.

  2. And then use below expression to find unique records based on Column “Service Item Group Code”

    uniqueDT = inputDT.DefaultView.ToTable(True,“Service Item Group Code”)

  3. And then use ForEach Row activity to count how many no. of rows are there for each item “Service Item Group Code”

      ForEach row in uniqueDT
             filterDT = inputDT.Select("[Service Item Group Code] = '"+row(“Service Item Group Code”).Tostring+"'").CopyToDataTable
    
             index = filterDT.Rows.Count
    
  • In First Iteration the count will be 4 and then add +1 to it and use Write Cell Activity and pass empty value in whole the row.

  • In second iteration the count will be 1 and you have to add this count to previous count and then add +1 as usual to add empty row.

So it will continue till all the unique items completed.

1 Like

Hello @lakshman, Kindly explain it… and check out my file

Test.xaml (10.1 KB)

CallReports.xlsx (45.4 KB)

Hi @lakshman, Can you suggest to this automation. How to insert blank row after category wise dynamically. Category row is not fixed it’s changeable.