How to update value into target cell when working with excel data by easy concept?

Help Studio
, ,
1

I think about how to deal with this case if i want to stamp value into a cell by filter data

Example.
image

as you see this is my excel data and then how can i stamp value into a cell by easy way concept by using something like WHERE in then stamp data to target cell.

The result will be like this
image

as you see i need to stamp ‘yes’ in column ‘complete’ by search data if column ‘name’ equal ‘john’

or if i want to update ‘yes’ data into where column ‘name’ equal ‘kean’ how can i do it?

Thanks,
Rugpong

2

Hi @lovepong66 ,
check with the below LINQ expression!

DtClone= readDt.Clone

(From d In readDt.AsEnumerable
Let u =If(d(“name”).toString.equals(“kean”),“yes”,string.empty)
Select DtClone.Rows.Add(New Object(){d(0),d(1),u})).CopyToDataTable

Where ReadDt is the readRangeDt

Regards,

1 Like
3

Hi @lovepong66

You can try this query in order to update the completed column based on the name:

Invoke Code:

yourDt.AsEnumerable.ToList.ForEach(Sub(row)
row("complete") = If(row("name").ToString.Equals(nameString),"yes",row("complete").ToString)
End Sub)

You can replace the nameString entity with the desired data from the name column & the query will update the complete column for the same name accordingly.

Hope this helps,
Best Regards.

1 Like
4

I think LINQ is powerful for helping developer to do many thing and i think your reply is works for this issue 100% by the way i just get in RPA for 6 months actually i thought easiest way for me should be something that easy to understand for newbie

example, here is my code

image
image1241×601 129 KB

after use read range i got the dt datable for using then i use Filter Data activities for filter and my concept is after filter i know there is will only one left record for me … and then i tried to use Write Cell Activities to up date into specific column row but the problems is it stamp on wrong row

That why i posted for any solutions if you need to solve this problem it like the open answer because
believe it so many way to do this.

Thank you for your answer.
Rugpong

5

Hi @lovepong66,
Please follow the below alternate step,

  1. Take build data table activity and pass the col name init and keep var type as string.
  2. Take Read Range activity & create var as DT.
  3. Take assign var and given name as counter & var type as integer.
  4. Use for each row Data table activity and pass data table var.
  5. Inside for each row, used If condition.
  6. In If condition write, if(row(“name”).tostring.equal(“kean”))
  7. In Then field, used write cell activity and pass all necessary data init.
  8. Keep else field empty
  9. Out side if activity, Increment the counter by 1

Hope this will help you :smiling_face:,
Thanks,

1 Like
6

Hi @lovepong66, I think you are dealing with the attended automation. Please correct me if my understanding is wrong.

You want to set “yes” value in the complete column when you searched for a name and if it is exist.

String Input = Your searched value

newDt = Dt.clone

newDt = If(Dt.AsEnumerable.Any(Function(x) x(1).ToString.ToLower.Equals(Input.ToString.ToLower)), (From d In Dt.AsEnumerable
Let x = If (d(1).tostring.tolower.equals(Input.ToString.ToLower), “Yes”,Nothing)
Let y = New Object(){d(0),d(1),x}
Select newDt.Rows.Add(y)).CopyToDataTable, Dt).

Thanks,
Najeer Shaik.

7

Oh i got it not testing yet but i’m sure it works!! it seem like so many step to do for just update data function … then i think i should be pick the way of invoke code is the best for to this.

Big Thank again.
Rugpong

1 Like
8

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