How to find top row respective values as per row 2 value

[Jeeru_venkat_Rao]

Hello friends,
I want to check TRx in column 2, if only TRx there then I want check column 1 value, whether that cell have “Aggregrate” word available or not.

For eg.

If My bot find “TRx” in G2, then bot will check in G1 for “Aggregrate” word available or not. If available then it will capture G3 value else it will check next column.

[Anil_G]

@Jeeru_venkat_Rao

Can you be a little clear…because when you explained in words you spoke about two different columns…and when you gave the cell Values you are giving different rows instead of sifferent columns…

So the comparision be on rows or columns?

Can you explain in detail what is the requirement and what you want to acheive

Cheers

[Jeeru_venkat_Rao]

I want to search TRx value in column2(B2), if its found then bot will check in column 1(B1) value whether “Aggregate” word contains or not, if yes then it will capture B3 value otherwise it do same thing to the next column.

I hope you understand my point.

[Anil_G]

@Jeeru_venkat_Rao

First of all B1,B2 and B3 belongs to same column…not different column…they belong to different rows…do you mean if B2 matches then check A2 for Aggregate and then check C2 value other wise move to next row…is this correct?

if yes…

then follow the steps

1. Read the data into a datatable
2. Use for each row in datatable
3. Use a if condition with currentrow(1).ToString.Contains("TRx")
4. on the then side use one more if condition with currentrow(0).ToString.Contains("Aggregate")
5. On then side of second if you can get Currentrow(2).ToString…On the else side no match found

Cheers

[Jeeru_venkat_Rao]

Thanks @Anil_G.

I will try this method and let you know if any problem.

[Jeeru_venkat_Rao]

Hi Anil,

I tried your logic but that is not working.
Have faced some issues:-

1. For each Loop runs only 3 times–> But it should run by total column count
2. A3(It will change in every loop) data not capturing.

[Anil_G]

@Jeeru_venkat_Rao

The loop i have given is on rows not on columns…can you please let me know if you need to check and modify based on columns or rows?

If its columns then the loop and what to do would be different

Cheers

[kumarankittravel (Ankit Kumar)]

In Microsoft Excel, you can use the INDEX and MATCH functions to find the top row’s respective values based on row 2’s value. The steps are as follows:

1. Choose a cell in which to display the result.
2. Fill in the blanks with the following formula: =INDEX(top row range, MATCH(row 2 value, bottom row range, 0))
3. Replace “top row range” with the range of cells in the top row where you want to find the value.
4. Replace “row 2 value” with the cell reference of the value in row 2 that you want to match.
5. Substitute “range of row 2” for the range of cells in row 2 containing the value you want to match.
6. Enter to display the result.

[Jeeru_venkat_Rao]

@Anil_G

My requirement is simple bro, I am going to elaborate it again.

Requirements:-

1. Bot should read A2 cell value, if only TRx word available then it will check “Aggregate” word in A1 cell. if that “Aggregate” word not found then it will capture A3 value. Bot should run/check A2 to M2 and check all above conditions.

This is the requirement.
Please try to understand the query and help.

Thanks in advance.

[Anil_G]

@Jeeru_venkat_Rao

Then please follow as below

1. Read the data into a datatable dt and makes ure to uncheck add headers check box as there are no headers in your table
2. Use for each activity with in argument is dt.Columns and change type argument to dataColumn from the for each activity properties panel
3. Use a if condition with dt.rows(1)(Currentitem.ColumnName).ToString.Contains("TRx")
4. on the then side use one more if condition with dt.Rows(0)(currentitem.ColumnName).ToString.Contains("Aggregate")
5. On then side of second if you can get dt.Rows(2)(currentitem.columnName).ToString…On the else side no match found

Hope this helps

Cheers

[Chirag_Shetty_Divakar (Chirag Shetty)]

Hey @Jeeru_venkat_Rao
Hope this helps you

forum ans.zip (68.8 KB)

Cheers.

[Jeeru_venkat_Rao]

Thank you so much @Chirag_Shetty_Divakar

Its working perfectly.