How to find a number and update the status as 'found' in excel without using loop

Hi, need some help related to excel automation.
In excel you have to update the status without using the loop. there are two columns i.e. Number and Status. and the Number contains 4 digit number it is repeated two or three times. user input the number you have to find that number and update the status found at every row where you find respectively. without using the loop.

Please help

1 Like

Hi,

Ca you share specific sample data? (Input and expected output)

Regards,

example.xlsx (8.3 KB)
Just attaching the excel for reference.
In the excel, there are 2 columns ‘Numbers’ and ‘Status’
‘Numbers’ column contains 4 digit numbers.
Task is to find the user provided number in the ‘Numbers’ column and update the ‘Status’ present besides as ‘Found’
This user provided number can be existing multiple times as well under the ‘Numbers’ column.
Wherever this number is found in the ‘Numbers’ column, we have to update the ‘Status’ with ‘Found’ value.
And this exercise needs to be done without using looping techniques

Hope I was able to describe the situation.

Kindly help

Hi,
Thank you for sharing. Hope the following helps you.

Sample20210611-4.zip (16.2 KB)

Regards,

4 Likes

Yes…it’s working! Thank you so much.
Just wanted to know regarding the query which you used to achieve this.

dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({r(“Number”),if(r(“Number”).ToString=strInput,“Found”,r(“Status”))},False)).CopyToDataTable

Can you plz let me know method is this?

Thank you once again

Hi,

it’s LINQ expression.

dt.AsEnumerable.Select(Function(r) means iterate each row in DataTable as r.

dt.Clone.LoadDataRow({r("Number"),if(r("Number").ToString=strInput,"Found",r("Status"))},False)

LoadDataRow method return data row of dt. and its contents are array of items : {r("Number"),if(r("Number").ToString=strInput,"Found",r("Status"))}
This means
Column0: r("Number")
Column1: if(r("Number").ToString=strInput,"Found",r("Status"))

Regards,

2 Likes

Thank you for sharing the knowledge

@Yoichi
nice explanation.
writing Linq is really challanging

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