How can I check if a specific value exists in an Excel sheet (return a boolean value)?

Hi, guys!

I’m stuck on how to do the following implementation. Could you please tell me how to solve it?
Thank you very much.

Requirements:

  • In one of the sheets, search by row name and select
    If the row name exists, return true. If the row name exists, return true, otherwise, return false.

  • The formula is contained in the corresponding cell. If some of the calculation items do not exist, they will be assumed to be zero and then calculated.

For example, in Sheet1 of the Master file, one cell contains [E Sales 1 - D Sales 1+ E Sales 2 - D Sales 2].
Sometimes Sales 2 does not exist, and in this case, we need to change the calculation in this cell to 【E Sales 1 - D Sales 1+ 0-0】.
How can I implement this?

※Sales 1 and Sales 2 exist in different Excel files. There are 3 sheets in that file.

@111962

Check below for your reference

Reference

Hope this may help you

Thanks

1 Like

Hi @111962

For check if the value exists in a sheet please try below steps,

  1. Read the excel sheet data in the datatable. Variable name dt

  2. Use if activity and in condition give,

Dt.asenumerable.any(function (row) dt.columns.cast(of datacolumn).function(col row(col.columnname).tostring.trim.toupper.equals(“your searchvalue”)))

It will return an Boolean value

Thanks

1 Like

Hi,

First read all the files and store them into different datatables.
2nd use below query to check whether this value exist in the datatable(that would be work excel sheet) it will return boolean

hasValue = dt.AsEnumerable.Select(function(r) r(“ColumnName”).ToString=“Value”).Contains(true)

3rd add if condition if(valueExist=true,value,0)

1 Like

Thank you for your quick reply.

I have tried to understand the above formula, especially the content in the Dt_TDI_Asset.AsEnumerable.Any(). and still a little bit confused.
Could you please explain the contents of the parentheses?

thank you!

@111962 yes Sure

Here is the steps that’s take place in thr query,

  1. Convert the datatable into an collection of datarows using asenumerable.

  2. Any method will return an Boolean (true or false) if atleast one condition is true inside the loop.

  3. Function (row) lambda expression used to loop the rows of the datatable.

  4. Dt.columns.cast(of datacolumn) used to convert datatable headers into a collection.

  5. Function (col) again lambda expression used to loop the columns of the datatable which already we converted into an collection.

  6. row(col.columnname).tostring.trim.toupper.equals(“your searchvalue”)) used to check if any cell in datatable contains the value we want to search.

So it will goes to first row, check all the columns in that row and then goes to second row and so on,if any cell contains the value we want it will return an boolean true else it will return false.

If you want to check only one column use this,

Dt.asenumerable.any(function (row) row(“columnname”).tostring.trim.toupper.equals(“your searchvalue”))

Thanks

1 Like

Thank you very much!! :bowing_woman:
I will try this later.

1 Like

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