How to copy cell value to another Excel sheet based on matching condition?

Help
,
1

Hi there,

I am having 2 excel sheets. “excela” which contains the source data. “excelb” which contains the criteria which value needs to be selected from “excela”.

My goal is: If excela.Value matches excelb.Value then copy the corresponding excela.ID to excelb.ID

Can anyone help on this?

Thank you

excela.xlsx (9.5 KB)
excelb.xlsx (8.0 KB)

2

Hi @dbcan,

Steps to follow:

  1. Read the first excel sheet and store it into dt1(datatable) using Read Range Activity
  2. Read the second excel sheet and store it into dt2(datatable) using Read Range Activity
    3.remove empty row in dt1.
    4.update ID fileds in dt1
    5.based on dt1 update the dt2 ID field
    6.using write range activity append the datatable(dt2) data to second Excel

For your reference
MatchCellValue.xaml (20.0 KB)

Regards,
Arivu

1 Like
3

Hi Arivu,

the workflow it is not working. Have you tested it?

4

@dbcan

1.Read the excela and store it in a DataTable1 by using ReadRange Activity
2.Read the excelb and store it in a DataTable2 by using ReadRange Activity
3. Extract the ID column from excel sheet a and store it in listA of strings
4.Extract the Value Column from excel shhet a and store it in listB of strings
5.Extract the Value column from excel sheet b and store it in listC of strings
6. Remove the Empty values from List a and List b by using Assign activity
7. In Assign int d -lstA.RemoveAll(Function(x) string.IsNullOrEmpty(x.ToString))
8. In Assign int e- lstB.RemoveAll(Function(x) string.IsNullOrEmpty(x.ToString))
9.Use one ForEach row Atcivity
10. Inside that use one If Condition. give List B in collections
11. In if Condition - item=ListC(List.IndexOf(item,ListB)
12.Use one Assign Activity
13.Inside Assign- DataTable2(List.IndexOf(item,ListB))(“ID”)
14.Use Write Range and to write DataTable2 in Excel shhet

Regards,
Mahesh

1 Like
5

@mahesh

I’ve never worked with list of strings and some other steps are not clear as well. Can you please attach the workflow?

Regards

6

followed the steps and keep getting errors from step 11, could you please post the workflow?

7

Hello, please did you find an alternate solution to this?

8

Hi Arivu, this isnt giving the correct output. could you kindly look through it again?

9

@ibiayo
Replace Step 11 with this

In if Condition - item=ListC(ListB.IndexOf(item))

Regards,
Mahesh

1 Like
10

Hello Maresh, i appreciate the quick response. However, i am still having the same issue

image
image1359×704 22 KB

Please help.

11

@ibiayo

Can you post your xaml

12

Sample66.xaml (10.1 KB)

Thank you so much.

1 Like
13

@MAHESH1
Sample66.xaml (10.1 KB)
Thank you so much. will appreciate any help

14

Guys,

If you need any excel, ms word, pdf, notepad, outlook activity/ functions then please drop me an email to have a deal.

I have 100+ activities which will make your job so easy.

Email : johntechuipath@gmail.com

Best Regards

John

15

@ibiayo

Now use the below xaml, I think it will work.
Sample66.xaml (12.0 KB)

Regards,
Mahesh

1 Like
16

@MAHESH1

Its still giving me this error.

image
image1882×755 38.6 KB

What i’m trying to do is put the column resp_code (from the first table) in the second table where rrn=refno
Firstexcel3.xlsx (8.8 KB)
secondexcel3.xlsx (8.8 KB)

17

@ibiayo

It was not thrown any exception here are you running the same code what I have sent

Regards,
Mahesh

18

@ibiayo

Give the correct path in Read Range Activities.

Regards,
Mahesh

19

@MAHESH1

Yes i’m running the exact code and the read range is pointing to the right path. the problem seems to be with this value

image
image1169×549 24.7 KB

20

@ibiayo

I gave the code for the excel sheet you posted as arequirement.

The code was not working because there was no Column RESP_CODE in SecondExcel 3.

Regards,
Mahesh