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

[Ani25 (Anisha)]

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

[Ani25 (Anisha)]

item file-

[THIRU_NANI (NaNi)]

Hey!

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

CurrentRow1("ID").ToString.Equals(CurrentRow2("ID").ToString.Trim)

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).....

Note:

City1-Coimbatore
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

Counter=Counter+1

Regards,
NaNi

[Ani25 (Anisha)]

(post deleted by author)

[Ani25 (Anisha)]

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

[kumar.varun2 (Varun Kumar)]

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)
	).CopyToDataTable

XAML for reference

FillValuesInAnotherExcel.xaml (9.5 KB)