Consolidate data from a table using Linq

[Lynx (Lnx)]

Good afternoon friends, a question please

I have an Excel table where the occupational medical analyzes of users have been recorded on the same day but at different times, which is why several rows with the same name have been generated, for example: user 1 exists in row 2 and the analysis was performed 1 (column B) but neither analysis 2 (column C) nor analysis 3 (column D) was performed, then it appears in row 4, where analysis 1 appears again because the test was already performed at an earlier time on the same day In turn, analysis 2, which is in column C, also appears in this same row 4. Finally, it appears in row 6, where analysis 1 and analysis 2 appear for the reason that we already explained, and analysis 3 appears in turn. in column D.

So what I want is for the rows to be consolidated taking into account the user (column A).

Attach an excel where I put a source table and the table I want as a result (second table)

I would like to do it with linq and have it generate a new datatable to be able to process it

Thanks in advance
for_Linq.xlsx (9.1 KB)

[ashokkarale]

@Lynx,

Here you go the LINQ query:

(From row In dtInput.AsEnumerable()
 Group row By usuario = row.Field(Of String)("usuario") Into userGroup = Group
 Select dtOutput.LoadDataRow(New Object() {
     usuario,
     userGroup.Select(Function(r) r.Field(Of String)("analisis 1")).FirstOrDefault(Function(a) Not String.IsNullOrEmpty(a)),
     userGroup.Select(Function(r) r.Field(Of String)("analisis 2")).FirstOrDefault(Function(a) Not String.IsNullOrEmpty(a)),
     userGroup.Select(Function(r) r.Field(Of String)("analisis 3")).FirstOrDefault(Function(a) Not String.IsNullOrEmpty(a))
 }, False)).CopyToDataTable()

Input:

Output:

Sample Code:
DataConsolidation.zip (11.4 KB)

Thanks,
Ashok

[Lynx (Lnx)]

Ashok Karale thank you very much for everything, it works perfect