I have first spreadsheet with duplicate “ID” Column and “Names” Column and i have second spreadsheet which is valid “Names” in Column , i want to check the first spreadsheet row by row “Name” with second spreadsheet which row is match with the second spreadsheet i need to take the Matched “ID” and if same “ID’s” in next rows delete the ID and Names. and save the vaild “Name” ID in New Spreadsheet. if the current row of “Name” Column was not match with the second spreadsheet i need to check if the next row of ID is same check the “Name” Column with second spreadsheet if name match get the ID and delete the Other same ID.
Maybe the result required could be achieved with Join Datatables activity and Group By methods/Remove Duplicate rows activity.
Use the Join Datatables activity with Left Join and First Datatable as Spreadsheet1 Datatable and Second Datatable as Spreadsheet2 datatable, Define the column names to check for Match.
With the Output of Join Datatables activity use Remove Duplicate Rows activity and check if it is the required output or let us know how does it vary from your required Output.
A correction from my previous suggestion is to keep the Join Type as Inner Join and perform the remove of duplicates based on ID column using Group By clause.
Let us know if the above workflow does not work for your case.
It uses the same implementation and also additional implementation with a Left Join performed on the Data and we only preserve the row values for IDs which do not have any value assigned on the 3rd Column (Other Datatable Name value).
