Search value in Excel Particular column and Print "Found" or "Not Found"

Help Studio
1

I am Getting a value from the Mail.
E.g. Invoice number : 1234567

input value is : 1234567

I have an Excel file Report.xlsx with a column name “Invoice Numbers”.
I need to check the input value in the column name “Invoice Numbers”.

If that value is found then Write “Found” in that Row to Column “Is Invoice found ?”
else
Write “Not Found” in that Row to Column “Is Invoice found ?”

Any help would be appreciated.

Thanks.

1 Like
2

Hey!

Try like this:

Take one assign activity create one integer variable Counter

Counter=2

Inside the for each row take one if condition

CurrentRow("Invoice Numbers").ToString.Trim.Equals(InvoiceNumbetrVariable)

In then block

Take one write cell activity in the String type “Found”
Range: “C2:C”+(Counter+1).ToString

Take one assign

Counter=Counter+1

In else block
Take one write cell activity in the String type “Not Found”
Range: “C2:C”+(Counter+1).ToString

Take one Assign:

Counter=Counter+1

This will update the data as Found if the Row value = Input Value
else
This will update the data as Not Found if the Row value != Input value

Try this and let me know

Regards,
NaNi

3

Thank you for the Quick reply, I will try this approach.

One more thing, If I found the Invoice number then I need to Copy all the Data of that Row and Paste to another Excel( FoundInvoices.xlsx) how can I achieve that ?

Thanks.

4

Hey!

For that we need to create a temp DataTable(Build DataTable)

Create the required columns-Output as -DtTemp

Now in then block

Try like this:

  1. Take one Add data row

In the ArrayOfRow mention like this:

{CurrentRow(0).ToString,CurrentRow(1).ToString,CurrentRow(2).ToString.....}

Keep this after the Write cell…

Note:
The column position of the temp dataTable should match with the required columns

Regards,
NaNi

5

I have tried this, in the log I can see it’s working fine but not sure about the output it’s generating in the Excel file.
Sharing the workflow and Test File.
Don’t know if doing any silly mistake.

Thanks
Number List.xlsx (8.7 KB)
Search Number in Column.xaml (10.2 KB)

6

Hi @Gagan_Chaudhari

I have done some minor modifications. Just check if it works for you

Search Number in Column.xaml (8.9 KB)

1 Like
7

Thanks for this quick Fix man.

Just another Q. it may helpful for me in the future learning.

If any value not Found the File1.xlsx then I need to check in the File2.xlsx.

How can I do that.

Thanks.

8

Hi @Gagan_Chaudhari

I have made some changes to you xaml. Please check

Search Number in Column.xaml (8.0 KB)

To your new question, you can read the both files, and then merge it in a single file and check for the invoice number and write the merged file in a single excel file.

Or

Without merging, first search in the first file. Keep a variable to track if the match is found, if the match is found in the first file do not go for the second file, else go for the second file.

9

Hello @Gagan_Chaudhari Please check the below workflow which checks in another excel if the value not found in the first excel

SearchNumberinExcel.zip (20.7 KB)

1 Like
10

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