Compare and update the column in excel

Hi All,

Book test.xlsx (9.5 KB)
Above sample is attached. Here 2 different sheets are there. In this excel
image
the column is null or empty and required values are present in sheet2 .
image
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

Hi @lakshmi.mp ,

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

Hi @supermanPunch ,
I tried using join left join .


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

@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

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.

1 Like

Hello @lakshmi.mp ,

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

Please refer to the below video for your reference :

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

Regards,
Rohith

1 Like

hi @EevaHanninen ,
I tried to download the attach xaml but its not opening. Because i am using studio.
image
What i need to do.
Thanks,
Lakshmi

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

Hi @rohith.prabhu ,

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

Thanks,
Lakshmi

1 Like

@lakshmi.mp ,

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

Thanks,
Rohith

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