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

Help Activities
, ,
1

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

image
image1920×1080 184 KB

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

2

item file-

image
image1920×1080 164 KB

3

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

5

(post deleted by author)

6

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

7

Hi @Ani25

You can use the LINQ for the task

image

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

8

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