Take a few values from one excel and fill the value in another excel based on certain criteria

I have two excel
named item file and product file
product file -

In product file based on the city and Id need to take the product value
and fill it in the item file to the respective id and city

item file-


Try like this:

Read product file-Output-DtProduct
Read item file-Output-DtItem
take one assign activity-Create a counter Variable-Type Int=1

take one for each row in datatable-Pass the DtProduct-CurrentRow1
take one more for each row in datatable-Pass the DtItem-CurrentRow2
Take one If Condition-Mention the condition like this


In then block you’ll get the matched records…

Now take the one more if condition inside the then block

CurrentRow1("City").ToString.Contains(CurrentRow2("City1").ToString.Trim) or CurrentRow1("City").ToString.Contains(CurrentRow2("City2").ToString.Trim) or CurrentRow1("City").ToString.Contains(CurrentRow2("City3").ToString.Trim).....


City2-Kerala so on…

In then Block:

Take 9 writecell

Value CurrentRow(“product”).ToString.Trim
Range: “C3:C”+(Counter+1).ToString

Value CurrentRow(“product”).ToString.Trim
Range: “D3:D”+(Counter+1).ToString


Take one assign and increment the counter like this



(post deleted by author)

f I use if condition the more activities need to be placed…
I need Linq query or someother alternative

Hi @Ani25

You can use the LINQ for the task


	From row In dt_Product
	Group row By 
	k = row("ID").ToString.Trim
	Into grp = Group
	Let cparr = 
		(From c In CityArr
		Let arr = (grp.Where(Function(gr) gr("City").ToString.Trim.ToLower.Equals(c.ToString.ToLower.Trim)).Sum(Function(grw) CInt(grw("Product"))))
		Select DirectCast(arr, Object)).ToArray
	Let pa = New Object() {k, grp(0)("Description")}
	Let ra =pa.Concat(cparr).ToArray
	Select r = dt_Item.Rows.Add(ra)

XAML for reference

FillValuesInAnotherExcel.xaml (9.5 KB)

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