Update Excel Column Based on Other Column without using for each loop

Help Activities
, ,
1

Hi,

I have excel in which i need to update ‘Status’ based on other 6 columns

  1. First I need to get the unique sample values from column A and apply the filteration on column A
  2. Sort the created date from oldest to newest
  3. Update the column ‘Status’ based on the following condition
    a. If (Stock-Requirement qty)>=0 then Status would be ‘A’
    b. If (Stock-Requirement qty)<0 and Column ‘Ob No’ and ‘Del Date’ are not
    blank then Status would be ‘B’
    c. If (Stock-Requirement qty)<0 and Column ‘PRO No’ is not
    blank and Unreleased is 0 then Status would be ‘C’
    d. If (Stock-Requirement qty)<0 and Column ‘PRO No’ is
    blank and Unreleased is 0 then Status would be ‘D’
    e. If (Stock-Requirement qty)<0 and Column ‘PRO No’ is not
    blank and Unreleased > 0 then Status would be ‘E’

Note - If Sample comes in multiple line then difference(Stock-Requirement qty) would be calculated from the Previous line result

Example - Sample Requirement qty Stock Difference(Stock-Requirement Qty)
135903 1 2 1
135903 1 2 0
135903 1 2 -1

Can you please help me to achieve the above by vb.net code using invoke code activity as the excel contains more than 10000 records

Attaching a sample data for your reference.

Thank you.
SampleData.xlsx (9.6 KB)

2

Hi @farheenfatma61,

What about this workflow

Input
image

Output
image

Code

If(CInt(row("Stock").tostring) - CInt(row("Requirement qty").tostring)>=0,"A",
If(CInt(row("Stock").tostring) - CInt(row("Requirement qty").tostring)<0 And Not String.IsNullOrEmpty(row("Ob No.").tostring) And Not String.IsNullOrEmpty(row("Del Date").tostring),"B",
If(CInt(row("Stock").tostring) - CInt(row("Requirement qty").tostring)<0 And Not String.IsNullOrEmpty(row("PRO No.").tostring) And row("Unreleased").tostring="0","C",
If(CInt(row("Stock").tostring) - CInt(row("Requirement qty").tostring)<0 And String.IsNullOrEmpty(row("PRO No.").tostring) And row("Unreleased").tostring="0","D",
If(CInt(row("Stock").tostring) - CInt(row("Requirement qty").tostring)<0 And Not String.IsNullOrEmpty(row("PRO No.").tostring) And CInt(row("Unreleased").tostring)>0,"E",Nothing)))))

Thanks,
Rajkumar

3

Hi

Actually i can’t use for each loop activity from UiPath as it is time consuming and data is huge.

I just want to achieve this by using vb.net/C# code to reduce the time .

I am not that much confident in writing the code as i am new to this.