How to get 2 rows for the same value of a column in Excel

Build
#1

I want to have two rows for each assignee in the attached excel file.

I can get the distinct value by using the below code but i need two values for each distinct value.

DT = ((From LineNo In DT.DefaultView.ToTable(True,“Assignee”).Select().ToList() Select (From row In DT.Select Where row(“Assignee”).ToString=LineNo(“Assignee”).ToString Select row).ToList(0)).ToList()).CopyToDatatable()

#2

If you are sure that there are two rows available for each assignee,

Just use similar query to load the second row -

DT1 = ((From LineNo In DT.DefaultView.ToTable(True,“Assignee”).Select().ToList() Select (From row In DT.Select Where row(“Assignee”).ToString=LineNo(“Assignee”).ToString Select row).ToList(1)).ToList()).CopyToDatatable()

Change in the query - ToList(1) //loading the second matched value

At the end append both Data tables DT and DT1.

Regards,
Karthik Byggari

1 Like
#3

Hi Karthik,

Thanks for the reply.

I had tried exactly the same but i got the error like

"Assign : Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index "

What to do for this to execute correctly? If you can quickly execute the solution once at your end then it will be very useful.

#4

First duplicate the DT to another data table. For example DTCopy = DT.

Without cloning the DTs, the queries won’t work. Because after first query, there will be only one Assignee row will present and the second query will return error.

DTCopy = DT

DT = ((From LineNo In DT.DefaultView.ToTable(True,“Assignee”).Select().ToList() Select (From row In DT.Select Where row(“Assignee”).ToString=LineNo(“Assignee”).ToString Select row).ToList(0)).ToList()).CopyToDatatable()

DT1 = ((From LineNo In DTCopy.DefaultView.ToTable(True,“Assignee”).Select().ToList() Select (From row In DTCopy.Select Where row(“Assignee”).ToString=LineNo(“Assignee”).ToString Select row).ToList(1)).ToList()).CopyToDatatable()

Regards,
Karthik Byggari

2 Likes
#5

Thank you so much Karthik. It worked fine.

Though It does not happen in my current requirement but Just asking another query… what if there be only one row available for some assignee?

#6

First get the duplicates into another data table,
then use the second query only on the duplicates data table.

Here I am giving the sample query which is in C#.

The second query has to run on duplicates below.

var duplicates = dt.AsEnumerable()
                   .Select(dr => dr.Field<string>("Assignee"))
                   .GroupBy(x => x)
                   .Where(g => g.Count() > 1)
                   .Select(g => g.Key)
                   .ToList();

Regards,
Karthik Bygggari

1 Like
#7

hello @Onkar_Nath_Sharma
email ids in attached excel file looks valid to me…
if this is the case… kindly remove the attachment as this is a public forum… and email spammers might get hands on it…

#8

Thank you for the flag

#11

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