Hi @sidb
Please find the solution with the approach mentioned by @ppr.
Reading the excel sheets and Merging the Data Tables
dtResult =
(
From row In dt.AsEnumerable()
Group row By k = row("ID").ToString.Trim
Into grp=Group
Let lt = grp.Select(Function(g) arrColSet.Select(Function(cn) Tuple.Create(cn, g(cn)))).SelectMany(Function(t) t)
Let ltf = lt.Where(Function(t) Not (IsNothing(t.Item2) OrElse String.IsNullOrEmpty(t.Item2.ToString.Trim)))
Let ra= arrColSet.Select(Function(c) ltf.Where(Function(t) c.Equals(t.Item1)).DefaultIfEmpty(New Tuple(Of String, Object)(c, Nothing)).First().Item2).ToArray
Select r = dtResult.Rows.Add(ra)
).CopyToDataTable
Refer the project
Project.zip (11.2 KB)