Update Excel Sheet

[anmita]

Hii ,

Actually we need to Update values based on two parameters

Example
If year is 2023 and month is 1 for NK01
Then it should return the cell value as C4

Have tried Vlookup but the challange there is we cannot provide both year and month parameters together.So if we add just month and we want to fetch the cell for 2023 suppose then it provides 3 different cell values because there are three different year in the file

Your suggestions are appreciated:)

Have attached sheet for reference
Test (1).xlsx (8.3 KB)

[Anil_G (Anil Gorthi)]

@anmita

Please follow the steps

1. Perform look up on the year first…say 2023…from which take the first cell itself say A4
2. Now perform the look up on the second column as there are 12 months restrict the look up to 12 cells like B4:B16
3. Now you would have the corresponding cell that is under required year and requried month

Basically to variablize youc an use like…

We already knw which column year and months are

So year always we will get from column A

so we can use "B" + Cellvalue.Replace("A","") + ":B" + (Cint(Cellvalue.Replace("A",""))+12).ToString This will restrict the search in only 12 cells from the first identified year cell

cheers

[anmita]

Hii @Anil_G

Thank you for your prompt response

Could you share a xml for Reference

Thanks and Regards

[ushu (Usha kiranmai)]

Hi @anmita How about trying the below query. It returns row index

Input.AsEnumerable.Tolist.Findindex(Function(r) r(0).Tostring.Trim.Equals("2023") And r(1).Tostring.Trim.Equals("1"))

Attached file for reference

SampleProcess.zip (9.5 KB)

[anmita]

Hii @ushu

Thank you for reference

Actually I need to find the cell value

Example year is 2023 and month 2 for YN01
Then we need to get E5 which is the cell value
As E5 will be used in Write Cell activity

Am able to find the Row index and the Column index
Just not getting the cell value

[supermanPunch (Arpan)]

@anmita

Then we need to get E5

@anmita

Just not getting the cell value

Hi @anmita ,

Do you mean the Cell Name ? E5 ?

If so, Could You check with the below Expression :

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(ColumnIndex+1)+(RowIndex+2).ToString

ColumnIndex and RowIndex are the values that you are already able to receive.

[anmita]

Hii @supermanPunch

yes we need to retrive cell value

Will try this and update

Thank you:)

[Anil_G (Anil Gorthi)]

@anmita

Please check this

this is how you can get the row index and then if we add 2 to the row index we can get the target row number

and the columns we already know which column to write…so we can get the final cell liek this “C” + (index+2).ToString

Check the xaml for reference

PepProcess - Copy (3).zip (13.5 KB)

Sequence 3 is what you can check…I harcoded 2023 and 2 …you can add variable and pass whatever values that you need

I tried with the above look up as well but it needs conversion of the column data types so came up with another approach

cheers

[anmita]

Thanks @Anil_G for Reference

[anmita]

Thanks @supermanPunch it worked