Group by 2 columns and match two excel files and add new column if matches otherwise add data row

I am having two excel sheets containing 3 columns each where two columns of each file have some common rows.

I want to generate a new excel where 1 excel remains as it is and 2 excel column matches and enters into 1 or the uncommon column is added at last.

If condition matches then column gets added otherwise a new row gets added into the excel.

The excel file is Groupby and match.xlsx (10.2 KB) where Sheet1 and Sheet2 are two sheets and Output shows the required output

The output should be like


Please help.

@Swara_Soni I have used Join Datatables Activity to join the Datatables based on the Condition that you have provided. It yields the output you need but not entirely as the values that you need to be in First Two Columns will be found in it’s duplicate Columns, as Join Datatable Activity does that by default if the Column Names are same.

Hence I have used Linq queries to determine the empty values of First Column that are present after joining to retrieve those values and Shift the values to the first two columns.

But this workflow does provide you the same output as you needed, but although it’s not in the order that you need.

Check this workflow: Revert back if you face any issues and Try the workflow for all kinds of data that you have.

The logic might not be entirely right, but we can try to fix it later based on the errors found. (9.6 KB)

Thanks for the workflow @supermanPunch
but when i am running the workflow in actual data it is merging the data tables but not putting values of common columns in same row

i am attaching the file here containing the sample data where the workflow is not working
Groupby and match.xlsx (11.6 KB)


The colored rows are the output rows which should be merged in one row.

Thanks again for the help.

@Swara_Soni ok. From the data that you have shown in the image, as you can see that there are two spaces after April in the 3rd row and one space after April in the 12th row, that is the reason it is treating it as different rows, Is that going to be the same always, or is that a Typo mistake ?, If that’s going to be the same always, then we would have to First replace multiple spaces with a Single Space and then perform the operation

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