How to merge two excel sheets when we have few items common between them

we have two excel sheets1 and sheet2. In sheet1 I have columns like “Item”,“Price”,“Quantity” and the same set of columns in sheet2. I want to make a 3rd excel sheet which will contain,both the common items and the rest of the items.I had used join data table activity but it is not working for me. I am attaching here all the files. Main.xaml (13.7 KB)
Excel1.xlsx (7.8 KB)
Excel2.xlsx (7.8 KB)
table3.xlsx (8.4 KB)
Plz. help!!

Hi!
Item can be repeated in Excel1 and Excel2 files ?

@Aleksey_M Yes,it is repeated

Hi,

You can read both excel sheets, let’s name them dt1 and dt2, use Write Range dt1 on the new excel sheet (excel sheet 3), then use Append Range using dt2 as your input on the same excel sheet.

Kindly update me if this solves your problem.

Thanks.

@jessbrian Thanks buddy!!! It worked… I have one more question I want to add the price and quantity of the common items in the same sheet(excel 3)… how will I do it? I am attaching the excel sheet 3…table3.xlsx (8.6 KB)

When you used Read Range it should automatically read all columns of the excel and as such should be part of the the output.

Note: Make sure you do not specify any value for the range as this will read all columns and rows. image

@jessbrian please do go through the excel sheet that I’ve attached below. There are two common items (fan). I want to add the quantity and price of those common items in the same sheet. How can I do it?table3.xlsx (8.3 KB)

How will you present it in the output file? That for common items like “fan” there should only be one row which is the total of the quantity and price for both files?

@jessbrian exactly my question. you got it right.

You need to rectify the requirements first.

If you proceed on just appending the files, then do the first solution. If you need to combine the all the common rows, you need to use For Each Row in the datatable activity to validate if a certain row item is identical, add the values (for price and quantity) if yes, otherwise skip.

Yoh, try using the custom package below by @balupad14 which may solve your scenario

https://gallery.uipath.com/packages/BalaReva.DataTable.Activities/ using Group By Aggregation (Sum,Count,Avg, Max, Min).