Compare and update the column in excel

[lakshmi.mp]

Hi All,

Book test.xlsx (9.5 KB)
Above sample is attached. Here 2 different sheets are there. In this excel

the column is null or empty and required values are present in sheet2 .

By matching the order column values need to be updated.
how to do this, I tried by taking 2 for each but its not working.
Expected output:
if in both the sheets order column is matching then need to update the values in sheet1 where Number column is blank.
Please help me.
Thanks,
Lakshmi

[supermanPunch (Arpan)]

Hi @lakshmi.mp ,

Have you tried using Join Datatable Activity, with Join Type as Left Join ?

[lakshmi.mp]

Hi @supermanPunch ,
I tried using join left join .

But values not writing [in the Number column .
What changes i need to do.
Thanks,
Lakshmi

[lakshmi.mp]

@supermanPunch ,
Is there is any other method other than join data table.
Please help me.
I tried using 2 for each loop but not working
Thanks,
Lakshmi

[EevaHanninen (EevaH)]

I would read Sheet2 into datatable variable first, then loop Sheet1 rows one by one and check those rows where Number is not set yet. If not set, check if row’s Order is found from Sheet2 data. If so, take the first match from that Order from Sheet2 data and update the cell value in Sheet1.

See example:

UpdateExcel.xaml (14.5 KB)
project.json (1.5 KB)

Note: In your Excel there was a space after Number column name in both, Sheet1 and Sheet2, I removed those for this example to work.

[rohith.prabhu]

Hello @lakshmi.mp ,

You can achieve this by using Merge Data Table with key option.

Please refer to the below video for your reference :

UiPath Tutorial || Day 51 : Merge Data Table Activity (Along AddwithKey) || Data Table Activities

Note : You can skip the initial part of the video and start from 10mins.

Regards,
Rohith

[lakshmi.mp]

hi @EevaHanninen ,
I tried to download the attach xaml but its not opening. Because i am using studio.

What i need to do.
Thanks,
Lakshmi

[EevaHanninen (EevaH)]

I attached project.json to my message. Close xaml, download project.json to the same folder as the xaml and try opening xaml again.

[lakshmi.mp]

Hi @rohith.prabhu ,

I followed this method it worked for me, thank you.

Thanks,
Lakshmi

[rohith.prabhu]

@lakshmi.mp ,

Great! You can mark it as solution so others can refer it in future.

Thanks,
Rohith