From input file Group rows having duplicate rows and update master according to master data

In the excel attached Find.xlsx (15.2 KB) , it contains 3 sheets “Input”, “Master” and “output”

Input

Master image

Output

The logic works like:

In “Criteria” = “RD” , Group columns AB,AC,AD,AE,AF
After grouping if the string has found duplicate rows then assign 1st row according to master and rest as “go”
if string is unique then assign value according to master sheet

In “Criteria” = “DD”, Group columns AB,AC,AD,AE,AF
After grouping if the string has found duplicate rows then assign 1st row according to master and rest as “go”
if string is unique then assign value according to master sheet

Here, in the output sheet – rows colored orange shows duplicate and in grey area shows non-duplicates.

Please help … how do i update the status accordingly.

@Swara_Soni
Variables:

Preperations:

  • clone dtOutput from dtInput , add the result column
  • use the master data for lookups in the form of a dictionary
    grafik

LINQ, doing the job:
grafik

  • iterate over dtInput
  • group data with the help of the concat trick
  • order groups by its first member position in original datatable
  • lookup the value from master
  • use lookup or go for constructing the target itemarray
  • add the constructed rowArray to the target Datatable
  • return the count of added rows

we do return the count in order to return anything (so called silly trick). As rows.add already adds the data, we do not need selectmany, copytodatatable

The order differs from your sample sheet, but the order requirements were not derivable from it.

Find starter help here:
Swara_Soni.xaml (9.6 KB)

2 Likes

@ppr Thanks a lot for the workflow. It worked perfectly in the actual case

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