Search for specific string in Excel and find the last value

@vvaidya @nadim.warsi @KarthikByggari @ClaytonM @arivu96 @nimin @aksh1yadav
Please help me to how to do below scenario

  1. find the 3 stars *** and scroll to the last figure on the right.

@loginerror can you please let me know how to search *** start symobl in excel contains


2.Check that last value is negative amount (<0) or Positive amount (>0)

Regards,
Naveen

Hi @Naveen.Ch,

Can you please elaborate your query…
in which column you want to search for 3 starts"***" ?

Thanks,

2 Likes

Hi @Naveen.Ch,

With the given information, i took into consideration that you know the column name or index of the column that has the *** value and the last column with value you want to check with (whether positive or negative.)

with that case, kindly refer to the below xaml and xlsx file for reference, Hope would help you…

Cheers…!

Main.xaml (9.2 KB)
Book1.xlsx (14.5 KB)

2 Likes

@Naveen.Ch,

Kindly revert, is there any other changes needed as @Gouda_6 mentioned or if there is any mistake in our consideration.

Cheers…

1 Like

@Naveen.Ch,

Buddy…is that working…are we correct…?

Hi @Palaniyappan,

Thanks for quick response
i dont no the column name and row name in excel
i have to search the 3 stars *** and scroll to the last figure on the right.and get the value

I want to search in whole excel sheet @Gouda_6

Hi @Naveen.Ch,

will no of columns remains constant to check for all the columns?

Thanks,

there is no column names in excel sheet

i have to find the 3 starts and need to scroll right to get the text

Hi @Naveen.Ch,

Try this,
Step 1 :use send hotkeys ctrl+f,search for ZZZ
Step 2 : find the cell then us short cut keys ctrl+rightarrow
Step 3: use send hot key to copy data Ctrl+c
Step 4: get copied data from clipboard
Step 5 :Check negative amount (<0) or Positive amount (>0)

Thanks,

1 Like

Let’s assume you use Read Range to store the data to a data table (I’ll call dt1)…
You can use .ItemArray to check for “***”, then based on if it contains the 3 stars, pull in the very last item in the row.

Additionally, we can start by filtering to only the rows that have “Yes”.

So, here is some pseudocode that might represent the steps needed:

Read Range

ForEach row in dt1.AsEnumerable.Where(Function(r) r(0).ToString.Trim.ToUpper = "YES").ToArray
    If activity: condition: row.ItemArray.Contains("***")
    Then
        If activity: condition: If(IsNumeric(row.ItemArray.Last), CDbl(row.ItemArray.Last) >= 0, False)
        Then <do something for positive value>
        Else <do something for negative value>

EDIT: you might also want to do something if the value is not a number.

Hopefully, that gets you rolling.

Regards.

2 Likes

Hi @ClaytonM

Thank you so much brief description

When i am trying to enter if condition failed due to no accessible Item accept this number of arguments

Hi @ClaytonM,
Please let me know how to proceed

  1. find the 3 stars *** and scroll to the last figure on the right.


2.Check that last value is negative amount (<0) or Positive amount (>0)

Regards,
Naveen

Sorry. it should be: row.ItemArray.Last

Give that a try.

or
row.Last

(i don’t have Studio open to test it)

Regards.

1 Like

for me first i have to search for 3 stars*** and get the last value

below condition to get the last value of specific row *** right?

If(IsNumeric(row.Item.Last), CDbl(row.Item.Last) >= 0, False)

If row.ItemArray.Contains("***")
the above part checks if the row has 3 stars, so then you can check the last value.

Essentially, you need to loop for each row where the first column has “yes”, check if the row has 3 stars, if so then look at last value in the row.

Refer to my pseudocode.

Regards.

1 Like

Hi @ClaytonM

first i am checking that yes condition in queues (if specific column yes means i have to search 3 stars and get the last value)

actually i dont have any column name in that excel sheet

Show me a screenshot of what you are trying to do in your workflow, and I can probably provide you some better help.

Regards. :grin:

1 Like

i want to get the last value of specific row if it contains 3 stars

1 Like