Insert number sequence in Column A based on column C and D

Help Studio
, ,
1

I need help solving an RPA, I get an excel with column A blank and I need to fill it in based on column C and D.
The condition is:
1st - When columns C and D are filled with numbers, the sequence in Column A is normal

2nd - When blocks with M in columns C and D, the sequence is repeated in Column A.

Note: When there is M in columns C and D, we must have T in one of the columns below until we find another M, or columns C and D are filled with numbers.

Before

Before
Before402×568 72.8 KB

After

After
After405×567 84.5 KB

2

Hi @Richarlei_Reis

I just tried and it works for me
Use this workflow

A workflow diagram illustrating the process of reading data from an Excel file, invoking code to manipulate the data, and writing the output to a new sheet in the same Excel file. (Captioned by AI)
A workflow diagram illustrating the process of reading data from an Excel file, invoking code to manipulate the data, and writing the output to a new sheet in the same Excel file. (Captioned by AI)505×567 15.5 KB

Invoke code:

Dim counter As Integer = 1
Dim pause As Boolean = False
dt_result  = dt.Clone()

For Each row As DataRow In dt.Rows
    Dim cVal As String = row("Debit").ToString().Trim()
    Dim dVal As String = row("Credit").ToString().Trim()

    Dim newRow As DataRow = dt_result.NewRow()
    newRow.ItemArray = CType(row.ItemArray.Clone(), Object())

    If pause Then
        If cVal = "T" Or dVal = "T" Then
            newRow("Sequence") = counter.ToString()
            
        Else
			counter += 1
			pause = False
            newRow("Sequence") = counter.ToString
        End If
    Else
        If cVal = "M" Or dVal = "M" Then
            pause = True
            newRow("Sequence") = counter.ToString
        Else
			pause = False
            newRow("Sequence") = counter.ToString()
            counter += 1
        End If
    End If

    dt_result.Rows.Add(newRow)
Next

' Assign the result back to dt if needed:
dt = dt_result

A dialog box displays invoked code arguments with data tables named dt_input and dt_output for input and output respectively. (Captioned by AI)
A dialog box displays invoked code arguments with data tables named dt_input and dt_output for input and output respectively. (Captioned by AI)885×229 9.74 KB

Input and output:

A spreadsheet displaying a series of transactions with dates, debit amounts, and credit amounts. (Captioned by AI)
A spreadsheet displaying a series of transactions with dates, debit amounts, and credit amounts. (Captioned by AI)337×533 4.78 KB

A spreadsheet table showing financial data, including sequence numbers, dates, debit amounts, and credit amounts, with uneven entries. (Captioned by AI)
A spreadsheet table showing financial data, including sequence numbers, dates, debit amounts, and credit amounts, with uneven entries. (Captioned by AI)407×553 5.52 KB

Please do mark it as a solution if it helps you

Happy Learning :innocent:

3

I’m getting this Exception

image
image1422×690 37.6 KB

I’m sending you the project.

I’m happy to try to help me

Insert number sequence.zip (16.7 KB)

4

Hi,

It’s necessary to set variable (dt_input) at DataTable property of ReadRangeWorkbook activity. Can you set it?

image
image1310×816 55.5 KB

Regards,

1 Like
5

@Richarlei_Reis

As @Yoichi said make sure add dt_input to the read range workbook

and also make sure to add add headers

The image shows a section of a workflow in a data automation tool where the "Write Range Workbook" activity is configured to write data to an Excel file named "Insert number.xlsx" with a specified range and data table, and an option to add headers is highlighted but unchecked. (Captioned by AI)
The image shows a section of a workflow in a data automation tool where the "Write Range Workbook" activity is configured to write data to an Excel file named "Insert number.xlsx" with a specified range and data table, and an option to add headers is highlighted but unchecked. (Captioned by AI)1088×287 18.1 KB

Output:

A spreadsheet displaying transactional data including date, debit, credit, value, and various other columns. (Captioned by AI)
A spreadsheet displaying transactional data including date, debit, credit, value, and various other columns. (Captioned by AI)770×530 8.44 KB

Hope this helps!

6

Yes, that’s right, Thank you!