Need to update cell in excel after finding row number using combination of more than two column values

[Shashi123 (Shashi)]

FirstName LastName EmployeeID
Ram Kumar
Ram Sharma
Ram Singh

I have an excel with above data. I need to update the cell in employeeID column for combination of FirstName and LastName. Basically i need to find the row number for combination of FirstName and LastName.

[jack.chan]

in for each row in datatable activity, set
CurrentRow("EmployeeID") = CurrentRow("FirstName").ToString+CurrentRow("LastName").ToString

[Nithinkrishna]

Hey @Shashi123

I guess you are looking for something similar - Get row number in excel for combination of Column Data - #4 by Nithinkrishna

Hope this helps

Thanks
#nK

[Shashi123 (Shashi)]

No Sir. What i am looking is getting row number. Actually i need the row number in an excel where input is Firstname and lastname. So basically i want to find row number where FirstName is Ram and LastName is Kumar. Depending upon input which is FirstName and LastName i want row number.

[Nithinkrishna]

Hey @Shashi123

Yep, the above link which has the code will return you index from which you need to just increment by one +1

Thanks
#nK

[Nithinkrishna]

Hey @Shashi123

Just posting the sample below,

int_ExcelRowIndex = dt.Rows.AsEnumerable.Select(Function(obj) (obj("Firstname").ToString + obj("Lastname").ToString)).ToList().IndexOf("TestName") + 2

Actually it will be +2 considering the headers, as updated above

Hope this helps.

Thanks
#nK

[Shashi123 (Shashi)]

Hi Sir,

What is IndexOf(“TestName”) in the code

[Nithinkrishna]

Hey @Shashi123

TestName is just the name value to compare, you can use your original name

IndexOf will give you the row index

Thanks
#nK

[Shashi123 (Shashi)]

Thank you sir for the help. What if i have the input in string format. Like i have Ram and Kumar as input. how should the formula look to get row number

[Nithinkrishna]

Hey @Shashi123

Just concat RamVar + KumarVar and pass it in the indexOf function argument.

Thanks
#nK

[Shashi123 (Shashi)]

I am getting an error which says AsEnumerable is not a member of System.Data.DatarowCollection

[Nithinkrishna]

Hey @Shashi123

That’s due to import issue, okay just replace AsEnumerable with Rows

Thanks
#nK

[Shashi123 (Shashi)]

Did the same. but still getting the error. If it is possible can u send me a xaml file with the code please. It would be a great help.

Thanks and Advance

[Nithinkrishna]

Okay sure @Shashi123

[Nithinkrishna]

Hey @Shashi123

Here you go…

nmnithinkrishna_XlRowIdxForMultiColMatch_08May2022.zip (8.3 KB)

Hope that helps.

Thanks
#nK