Create column as per Column's value and add data for same Employee in single row


I want to read rows and create a new column as per Column C’s value (e.g. Paid, Not Paid) and add the value in a single row for the same Emp_ID. I’ve attached my Input sheet and I want the data like the Output screenshot.





Thanks in advance.

Have a try on following

Prepare with build datatable the target data Column structure - dtResult
Cols: Emp_ID, EMP_Name, Paid, NotPaid
do not add any rows on it, we just do need the structure

Assign activity
Left side: dtResult

(From d in YourDataTableVar.AsEnumerable
Group d by k=d(“Emp_ID”).toString.Trim intor grp=Group
Let rp = grp.Where(Function (r1) r1(“Pay_Code”).toString.Trim.Equals(“Paid”))
Let rnp = grp.Where(Function (r2) r2(“Pay_Code”).toString.Trim.Equals(“Not Paid”))
Ler vp = rp.Select(Function (v1) v1(“Total”).toString).DefaultIfEmpty("").First()
Ler vnp = rnp.Select(Function (v2) v2(“Total”).toString).DefaultIfEmpty("").First()
Let ra = new Object(){k, grp.First()(1), vp,vnp}
Select dtResult.Rows.Add(ra)).CopyToDataTable

Hi @RohitK !
Sure, here is a suggestion: Paid_or_not_paid.xaml (7.0 KB)

Let us know if it does not work as expected or if the code needs more explanation :grinning_face_with_smiling_eyes:

1 Like

@Hiba_B It worked as I wanted. Thank you so much. :blush:

@ppr Thanks for your help. I really appreciate it.

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