Comparison Between 4 different Excels

Help
1

Hi All,

I have a case where I need to compare 4 or 5 different excels and get the common names from these 4 or 5 excels and write those common names into new excel. Also, need to remove the common names from those 4 or 5 excels.

Thank you,
Ram

2

HI @ramsaiamrutham

If you want to find common names between those excel sheets, we can use some data table functions here.

Steps.

  1. Read all four excel sheets using Read Range activities and get the data onto separate datatable variables.
  2. Use Join Data Table activity to join two datatable variables to get common values in between those two.

Use the Inner Join to join and get only the common records between the two to another datatable

  1. Use that datatable in a Write Range activity to write the common data onto an excel file.

  2. For removing those records from the source excel file, you can try out the below example.

This is another approach

Let know if this works out for you

If this works for you, please let know others as well by marking the answer as the solution :slight_smile:

1 Like
3

Hi Lahiru,

It works for me until Step 3.

But from Step 4 that is not what I need.

I need to remove the common names from DT1 and DT2 and Create a new DT with Common names.

Thank you,
Ramsai

4

Hi @ramsaiamrutham

For the 4th point, you can slightly change the above approach. By step 3, you have a separate datatable that holds common values right. So what you can do is, use that as a reference to find the common value row in your other datatable that holds all excel data.

  1. Use a For Each Row to iterate in the datatable that holds common records
  2. In the loop, use another For Each Row to iterate your datatable that holds excel data.

So basically the idea here is: get one common record, search for it in the datatable that has all data, once located, delete it from the datatable. Get the idea?

  1. Now inside that second loop, include a IF activity to check whether its a matching record. Assume the loop item identifier variable is dt1_row and its dt2_row in the second. You are just going to see whether it matches like this

    dt1_row("ColumnName").toString.Equals(dt2_row("ColumnName").toString)

This will check whether that row contains the matching record
In the IF condition, use can use the Delete Data Row activity as shown in the above solutions to remove the row from the data table.

  1. So once you are done with the loop, use another Write range activity to write the data back to a excel file. This excel file will only have records that do not match with other excel sheets.

Got the idea?

1 Like
5

@ramsaiamrutham

Dt1 =excel1
Dt2 =excel2
Dt3 =excel3
Dt4 =excel4

Now, just use following xaml carefully

Component for Two Datatable Column Match and Get The Matched Records.xaml (5.9 KB)

1.compare dt1 and dt2 and store output in outdt1dt2 (outdt1dt2 is datatable variable)
2. Compare outdt1dt2 with dt3 and store result in outdt1dt2dt3 (outdt1dt2dt3 is datatable variable)
And so on…

But i also wouder if you want only non-matched items then following xaml would be helpful…

Get Non-matched Items.xaml (9.7 KB)

If you are following steps given @Lahiru.Fernando then just use following
1.Read all excel in datatable
2.Join all datatable into one single datatable
3.use following xaml to remove duplicate( in other word to get unique records)

Get Unique Values.xaml (5.4 KB)

Just let me know what helps you. :relaxed:

2 Likes
6

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