How do i manipulate my excel

Help Studio
, ,
1

I have 2 excel sheets, One contains all the data and another contains some numbers

How can i check if the number is positive or negative number which are present in 2nd sheet and Crweate a new column as Status , and write the status as NEGATIVE , POSITIVE in 1st excel sheet
2nd exce input:

Data
-1
-3414
-141

1st excel output

Status
Negative
Negative
Negative

The catch is they shou;d be mapped with id which are present in both the sheet
So these ID are jumbled in 2nd sheet in a column

How can i map them and get the status?

2

@Goku
Read 2 sheets by using read range

1.add data column for first datatable

2.Use for each row in datatable activity for sheet2

Place if activity

Condition
Current(“ColumnName”).ToString.Contains(-)

Assign

Firstdt.rows(idx).item("columnname)=“negative”

Else

Firstdt.rows(idx).item("columnname)=“positive”
Cheers

Here idx is the output of for each row datatable activity

3

@Goku

You can try something like this

  1. Read both the datatable into dt1 and dt2
  2. Now use invoke code activity with dt2.AsEnumerable.ToList.ForEach(sub(r) r("Status") = If(dt1.AsEnumerable.Where(function(x) x("ID").ToString).Count>0,If(Cdbl(dt1.AsEnumerable.Where(function(x) x("ID").ToString)(0)("Number").ToString)>=0,"Positive","Negative"),""))

In invoke code send the dt2 as in/out argument

Cheers

1 Like
4

@Goku

for reference check the below Xaml

Main.xaml (17.2 KB)

cheers