Excel Comparison Issue

Help
1

Hello,

I am trying to compare 2 excel files based on a unique field ID and then compare 2 columns of different excel sheets ‘Status’ to check if there is any change in the status of a particular defect for a specific ID and then bring in only those ID’s which had their statuses changed. How can i achieve this?

Ex: Sheet1: DefectID, Name , Product ID, Status, Comment
Sheet2: DefectID, Small Description, Priority, Status, Changed by

So i want all those Defect Id’s with all other fields whose Status has changed.
Please help me to get over this conundrum.

1 Like
2

Buddy @npatni

Welcome to uipath community buddy and thats a great question to start with
But i would like suggest one more option we have like lookup datatable activity, kindly find the below steps that be would be a add on and you can make use of it you want buddy

  1. store them in DT1 and DT2 as you did earlier
  2. Use a for each row loop with DT1 as input
  3. use a look up datatable activity with these properties
    LOOKUP.zip (14.0 KB)

Thats all buddy few steps isn’t it…
Cheers… @npatni

3

First, get All ID’s present in both Sheets in a Datatable, let’s say
After that, For each desired row in Sheet1, filter in Created Datatable by ID and compare if Status from Sheet1 == Status from Datatable.
I think It could be one solution…

4

Hi Palaniyappan,

Thanks a lot bro for your quick reply but i am still not able to successfully run my desired output on my set of records with your Logic. I am getting repetitive output since my Status is not Unique compared to your Invoice Number’s.

See the below set of Data for 2 excel files that i am comparing

File-1:

Sr. No. Defect_No Status Raised_By

  1.    DEF1101      Open           Micheal

  2.    DEF1201      Closed         Johnny

  3.    DEF1301      Rejected      Darshu

  4.    DEF1401      Open            Sakhiya

File2:-

Sr. No. Defect_No Status Raised_By

  1.    DEF1101      Open           Micheal

  2.    DEF1201      Closed         Johnny

  3.    DEF1301      Rejected      Darshu

  4.    DEF1401      Closed          Sakhiya

As you can see the Defect status of DEF1401 is different in both files so i would require this Defect along with all the other relevant field details in my output.

Thanks in Advance!!

1 Like
5

Here is your xaml buddy @npatni
its resolved
task.zip (14.5 KB)

Kindly try this and let know buddy
Cheers @npatni

6

were you able to get now buddy @npatni

7

Hi Palaniyappan,

This exactly solves my problem buddy. You just made my Weekend bro. Cheers!!

Regards,
npatni

1 Like
8

Fantastic
Cheers buddy @npatni
Keep going

9

Hi Palaniyappan,

One more query bbuddy, now that i have the data generated from ur logic, i want to store the output into Data table. I tried doing it with Generate Output Data table however with that i am getting no separation between column’s and also the text inside the description field is separated by coma’s.

Example:

DEF11579General,Ledger,issue,to,be,resolved

As you can see the defect number and the description are from separate columns however there is no gap between them and then the rest of the description is from one column which is getting separated by a comma.

So the format of this output is not correct hence i cannot use it further and use Write range acitivity to paste it in excel.

Help me solve this one as well buddy! :slight_smile:

10

No Worries buddy
Kindly follow these procedures that could help you do this

  1. once you read the excel with read range activity and getting the output variable named outdt (that can be any one of the excel application scope) and after getting that output datatable, use a assign activity to clone that datatable buddy
    like this where finaldt is a variable of type datatable which is clone with same structure of the outdt obtained from the read range of first excel
    finaldt = outdt.Clone
  2. then after this use clear datatable with input of finaldt. to remove the rows present in that table and thus we have only the column with their headers alone
  3. Then once after the validation, inside the if condition, instead of write line we have now, use add data row activity buddy and pass the input in arrayrow property budy like this
    {row(0),row(1),row(2),row(3),row(4)} and datatable as finaldt
    where row is the current row in iteration of for each rowloop
    0,1,2,3,4 are the column index thus we get the value of each column in a single row
    and this would add the row one by one to the finaldt

so you would get the output now as datatable buddy
Thats all buddy you are done
Cheers @naptni

1 Like
11

Hello Palaniyappan,

You’re the Saviour Man! After 2 weeks of hard work i am almost done with my project with your help! Thanks a lot buddy!!

Regards,
npatni

1 Like
12

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