Greetings all i have a situation where i need to compare 2 excel spreadsheets in 2 excel files with particular column names such as account number and case name and need to write down the uncommon data into the new excel sheet it will be great if some one helps out thankyou
Hi,
Can you share specific input and expected output?
Regards,
Try this-
- Drag and drop the “Excel Application Scope” activity to your workflow and provide the file path of the first Excel file as the input.
- Within the Excel Application Scope, use the “Read Range” activity to read the data from the first Excel file and store it in a DataTable variable (let’s say
dt1
). - Repeat steps 1 and 2 for the second Excel file, storing the data in another DataTable variable (let’s say
dt2
). - Use the “Filter Data Table” activity to filter
dt1
based on the specified column names (e.g., ‘account number’ and ‘case name’) to extract the relevant data. - Similarly, filter
dt2
using the same column names. - Use the “Join Data Tables” activity to join
dt1
anddt2
based on the specified column names. Select the join type as “Full Outer” to include all rows from both tables. - Add a “Filter Data Table” activity after the “Join Data Tables” activity to filter the joined table. Specify the condition to filter rows where any of the specified column names is empty. This will extract the uncommon rows.
- Finally, use the “Write Range” activity to write the uncommon data (output of the “Filter Data Table” activity) to a new Excel file.
Thanks!!
Follow the below steps to achieve the output:
- Read Range Workbook activity:
Input: File path of Excel File 1
Output: dataTable1 - Read Range Workbook activity:
Input: File path of Excel File 2
Output: dataTable2 - Filter Data Table activity:
Input: dataTable1
Output: filteredDataTable1
Condition: ColumnName1 = “account number” And ColumnName2 = “case name” - Filter Data Table activity:
Input: dataTable2
Output: filteredDataTable2
Condition: ColumnName1 = “account number” And ColumnName2 = “case name” - Merge Data Table activity:
Input1: filteredDataTable1
Input2: filteredDataTable2
Output: uncommonDataTable - Filter Data Table activity:
Input: uncommonDataTable
Output: uniqueRowsDataTable
Remove Duplicate Rows: True
Columns: ColumnName1 (the column that uniquely identifies each row) - Write Range Workbook activity:
Input: File path of the new Excel file
Sheet Name: Specify the sheet name
DataTable: uniqueRowsDataTable
Hi so actually the requirement is that there will be 2 excel files which contains multiple sheets from that i need to select 2 sheets which are ineligible notices and upcoming settlerments those two should be compared with previous months sheet with using column names as account number and case name and then need to filter the uncommon data and write it to new excel sheet for some reasons i can’t share the files now
Hi i have tried it but it is not working iam just getting column names as the output
Hi thanks for the reply but my requirement is that i need to compare 2 excel sheets from 2 excel files out of those 2 sheets which have multiple columns in them from which i need to compare only case name and account number as column the column position is not fixed and after comparing those 2 columns i need to filter uncommon data and write it to new excel file
When you use Filter Datatable activity irrespective of the number of columns you have in the excel your datatable will get filtered with whatever columns you want. And you need to use Filter Datatable under For each row in datatable activity. Still facing the same issue kindly share the input excel files and the requirment. I will get the solution for same.
Regards,