Mapping columns in excel

Help Activities
, , ,
1

Hi ,

I have two sheets with one common column.Now i need to read value from one column and search that value in 2nd sheet and if i get it ,copy the respective row and paste in first sheet in respective columns

SHeet 2
id name college
1 x xyz
2 y zzyc

sheet 1
id name college
3 [to be filled based on id match in sheet 1]
2 [to be filled based on id match in sheet 1]

Any leads would help

I don’t wanna use any for each loop coz its a huge data.wanna if there is any simple way to avoid overhead to excel

2

Hi @ranjani try this

  1. First read the two sheets and store in dt1 and dt2 differently.

Then use join linq query by using assign activitiy

dt3= (From x In dt1 Join y In dt2 On x(“id”).tostring Equals y(“id”).tostring
Select dt3.Clone.LoadDataRow(New Object(){y(“id”).tostring,y(“name”).tostring,y(“college”).tostring},False)).CopyToDataTable

Where dt3 is the output datatable where it is define with column name is, name and college while building

Try this

Regards

Nived N :robot:

Happy Automation :relaxed::relaxed::relaxed:

1 Like
3

i get below error.I think its because its checking first row from x to first row in y.is this right?it should if that id is available elsewhere in the same columns,just like contains
RemoteException wrapping System.NullReferenceException: Object reference not set to an instance of an object.

4

Hi @ranjani
sorry for late response
check this workflow
sample.xaml (8.3 KB)
data.xlsx (8.3 KB)

Regards,
Nived N
Happy Automation

5

Thanks for your inputs.But got same error because of saving result in new table rather the dt if sheet 2…sheet 2 has other columns along with it but need to fill only two columns…

Also i don’t need to build a datatable .i just have to read two datatable from two different sheet find for match from first datatable and append the respective rows in sheet 2 datatable

eg

sheet 1
id name college
1 x xxx
3 y yyy
4 z zzz

sheet 2
name college place id country
qwe 3 ind
wer 4 ind
ywe 1 ind

output:
sheet 2
name college place id country
y yyy qwe 3 ind
z zzz wer 4 ind
ywe 1 ind

Now i have just changed the dtresult to dt of sheet 2 and got output in different order…now when pasting in sheet to it erases the header and starts pasting from header…also next three rows data is erased