Find in Excel value stored in variable and take out corresponding value

Help
1

Hello,

Can you please advise.
My case:
I’m taking a specific value from SAP and gonna store it in variable “Val1”. Then I have to go to Excel (I have to columns). I need to find “Val1” in column 1 and then store in another variable “Val2” corresponding value from column 2.
Thank you for all suggestion.

Gosia

1 Like
2

Hi @niteckam,

Please check below link:

3

I’m trying to understand…

  • I store my 1 value in Variable “value1”
  • Then I’m going to Excel using ReadRange and I store variable DataTable as “TDlist”
    I have to find "value1’ in column 0 and assign corresponding value from column 1 to my another Variable “value2”

Should I work on variable DataTable as “TDlist” or should I convert DataTable into DataRow
I’m thinking about
Assign
value2 = TDlist.select(…)

Please advise
Gosia

4

Hi @niteckam,

My .xlsx file has 3 columns. I need to update status column = “Name Found” for all the records where
Name column = “Employee Name”
The workflow is UpdateMatchingExcelCell.xaml (11.5 KB)

I am finding the index of the marching rows and specifying it as Range for the write cell activity.
The +2 with the row index is to match

  1. Datatable row index starts from 0 and Excel cell range starts from 1
  2. The header specifying in the .xlsx file will occupy the the first column there by increased another count.

Hope this helps!

5

Hi @Madhavi

Thank you, this not help at all…
I’m not good at Excel application
Seems to be so easy but how to find a specific value from the first column and then store in variable the value from corresponding second column…

6

@niteckam

Your requirement is,
I store my 1 value in Variable “value1”
Then I’m going to Excel using ReadRange and I store variable DataTable as “TDlist”
I have to find "value1’ in column 0 and assign corresponding value from column 1 to my another Variable “value2”

I have updated the workflow to match your requirement. UpdateMatchingExcelCell.xaml (9.9 KB)

If still this is not helpful, please share your excel file and specify which column you need to compare and which column you need to update.

7

HI @Madhavi

Looks like this is what I need.
I have to check couple of times, putting this into my workflow but it is super cool.

Thank you

8

Hello @Madhavi,
Hard to say but can you advise me in one more case. Excel again…

I have a DataTable, attached excel file.
I need to check row by row, focusing on the column “Status” and check if this column contains one of the following numbers (in many situations I have more rows with those numbers but I need to find only the first one) :
601, 610, 700, 710, 720, 730, 740, 750, 760, 770, 790
When the number from the above list will be found I want to finish the process and store this number in variable.
I’m thinking about For Each and then Do While

export1.XLSX (10.0 KB)

9

@niteckam
If you know the values that you need to check in the Status column,
Then you can use datatable.select() with or conditions to all those values. But this solution is not recommended if the number of values that you need to check is significantly high.
Hence,
Store the numbers to be searched in an array “arTransactions”
use below dtInputData.AsEnumerable().where(Function(s) arTransactions.Any(function(t) t.Contains(s(“Status”).ToString))).FirstOrDefault()

This should give first matching row.

10

Hi @Madhavi

Can you advise

3
3819×649 30 KB
4
4900×339 23.8 KB

11

@niteckam
You might have copied the code and pasted in the assign statement. In such cases, “” will be taken as non readable. remove the quotes around Status and enter it manually.
And This query should be specified on the right hand side of the assign statement and the variable into the left side of the assign statement.

12

Hi @Madhavi

Indeed almost done.
I’m not sure about the type of variable that is assigned to the formula
Which type can be use when I assign to DataTable

Capture5
Capture5817×471 31.3 KB

13

@niteckam Variable Type should be DataRow
Can you please post the full content of the assign statement,
gooSTATUS = dtInputData.AsEnum…

14

Capture34
Capture34924×457 42.7 KB

When I run this is the exception
Capture44

15

Before calling this query, Check whether the input data contains any records and is there any record matches the search criteria in your input data.

16

It does, now when I run have below output

Capture55
Capture55872×581 40.2 KB

17

@Madhavi
?

18

@niteckam As per the query, the first matching data row is returned.
You can check if(goodStatus is nothing)
// Record doesn’t exists
else
// Record exists

Are you looking for anything else here?

19

Hi @Madhavi

Looks like this work properly. But I need to store in the variable (can be my goodStatus) the value that was found in the excel file this one from the Array, Let say 700 to be able to use it later on. Now when I use Write line activity and have goodStatus I have output System.Data.DataRow instead of 700

21

Thank you @kritesh
However I’m close to the expected results, and not sure if your advise can be applicable now