How to combine duplicate rows into one (keeping unique values only)

Hi All,

I have filenames as shown in “Input Format” and I need filenames as shown in “Output format”
PFB the 2 formats.
Input Format:

Output Format:

Refer the workflow
sample.xlsx (9.8 KB) Sequence3.xaml (12.3 KB)

Output in Sheet2

have a look on a dynamic approach:

  • as FilePath is case insensitive (more or less) in Windows we do harmonize it on Uppercases and do create a List of distinct path names

  • on a prepared datatable we add dynamicly the no. of different filepaths as columns

  • Also we do create a lookup dictionary giving us the column index for a particular filepath

with the help of LINQ we do a groupby on the filenames (harmonized to uppercases)

On last step

  • we iterate over the found groups
  • using the lookup dict to insert the filepath in the rigth column
  • adding the constructed datarow to the result datatable:

Reduced sample input data:


Find starter help here:
GroupBy_1Col_TransposeGrp.xaml (14.6 KB)