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

excel
activities

#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


#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


#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


#10

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

Please help.


#11

@ibiayo

Can you post your xaml


#12

Sample66.xaml (10.1 KB)

Thank you so much.


#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


#16

@MAHESH1

Its still giving me this error.

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


#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