Sorting datatable according to multiple columns

[arun.v7]

I want to sort the datatable in such a way that the resulting datatable will have data arranged in the order of first column and the same values in second column should be grouped together.

Initial datatable

GN	JMBWN	Name
1	BBBB	Arun
1	BBBB	Ashwin
1	CCCC	Vishwa
2	DDDD	John
2	BBBB	Sam
2	DDDD	Jain

The output should look like

GN	JMBWN	Name
1	CCCC	Vishwa
1	BBBB	Arun
1	BBBB	Ashwin
2	BBBB	Sam
2	DDDD	John
2	DDDD	Jain

I tried some query but not able to get this desired result.

The query that i tried is
my datatable name is dtAdultTable.

“dtAdultTable.AsEnumerable.OrderBy(Function(x) x(0)).GroupBy(Function(x) x(1)).SelectMany(Function(x) x).CopyToDataTable()”

[ppr (Peter Preuss)]

We can use the Query Syntax

(From d in dtAdultTable.AsEnumerable
Let s1 = CInt(d("GN").toString.Trim)
Let s2 = d("JMBWN").toString.Trim
Order by s1,s2
Select r=d).CopyToDataTable

[lrtetala (Lakshman Reddy)]

Hi @arun.v7

Can you try below query

dtAdultTable = dtAdultTable.AsEnumerable().
    OrderBy(Function(row) row("GN")).
    ThenBy(Function(row) row("JMBWN")).
    CopyToDataTable()

Regards,

[rashmisingh2695 (Rashmi)]

Hi @arun.v7

You can try this Linq
dtAdultTable.AsEnumerable().OrderBy(Function(row) Cint(row(“GN”))).ThenBy(Function(row) row(“JMBWN”).ToString).CopyToDataTable

[arun.v7]

@lrtetala

CopyToDataTable()

iam getting the result like this,
1,BBBB,Arun
1,BBBB,Ashwin
1,CCCC,Vishwa
2,BBBB,Sam
2,DDDD,John
2,DDDD,Jain

But i want the same values in second column to be together, also the first column should be in order.

[arun.v7]

iam getting the result like this,
1,BBBB,Arun
1,BBBB,Ashwin
1,CCCC,Vishwa
2,BBBB,Sam
2,DDDD,John
2,DDDD,Jain

But i want the same values in second column to be together, also the first column should be in order. Here the issue iam facing is that always when i sort the (2,BBBB,Sam) is not coming together with the other BBBB values. But when i sort it and achieve this condition, the other condition that the first column should be in order will break.

[arun.v7]

iam fairly new to UiPath, please let me know how to use this code?

[ppr (Peter Preuss)]

same as you used the Method syntax, Assign Activity

[HowTo] - First Start with LINQ (VB.Net)

LINQ is part of the .NET framework and offers capabilities for interacting with data, objects or other sources within a particular syntax. This HowTo introduces on starting with LINQ (Language-Integrated Query). Current Version of this topic is DRAFT and will be finalized / enhanced soon Introduction Typical tasks for implementing parts of an automation project are interacting with e.g. with data in a datatable or items from collections. LINQ can help to implement it into a compact and standa…

[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

[arun.v7]

@ppr

Select r=d).CopyToDataTable

I tried the solution suggested by you, but iam not getting the second column values together when sorting the first column. Basically i want the same values in second column to come together while maintaining the order in the first column

[rashmisingh2695 (Rashmi)]

Can you try this and let me know if it works
dtAdultTable.AsEnumerable().OrderBy(Function(row) Cint(row(“GN”))).ThenBy(Function(row) row(“JMBWN”).ToString).ThenBy(Function(row) row(“Name”).ToString).CopyToDataTable

[arun.v7]

Sorry i think you misunderstood when i said second column. The column “Name” is not relevent when it comes to my sorting condition. I meant first column as “GN”, and the second column as “JMBWN”.

[ppr (Peter Preuss)]

@arun.v7
keep one thing in mind:

@arun.v7

The output should look like

GN JMBWN Name
1 CCCC Vishwa
1 BBBB Arun
1 BBBB Ashwin
2 BBBB Sam
2 DDDD John
2 DDDD Jain

grouping on first column and sorting on first column will create the series
111,222 …

sorting on second column you should decide the criteria for the sorting Ascending, Descending

maybe the following is needed:

(From d in dtAdultTable.AsEnumerable
Let s1 = CInt(d("GN").toString.Trim)
Let s2 = d("JMBWN").toString.Trim
Order by s1,s2 DESCENDING
Select r=d).CopyToDataTable

But we cannot do:

UPD1 - Visualizations:

[shetyetejas557]

Use Assign activity.
dt1=(
From row In dt
Order By row(“GN”),row(“JMBWN”)
Select row
).CopyToDataTable

[arun.v7]

I just gave the value as CCCC, BBBB, since i cannot paste the actual data here. The only criteria for me is that the same values should be grouped together and also the first column must be in order. Is this possible?.

[ppr (Peter Preuss)]

@arun.v7

I just gave the value as CCCC, BBBB, since i cannot paste the actual data here.

then we recommend to reformulate the requirement sharp and clear or to provide matching sample data

@arun.v7

The only criteria for me is that the same values should be grouped together

in general we address by

• grouping data
• sorting data

[J0ska]

I don’t think there is any reliable algorithm to fulfill your requirement.

E.g. below input data can’t be sorted according your requirement
1,DDDD,Arun
1,BBBB,Ashwin
1,CCCC,Vishwa
2,BBBB,Sam
2,DDDD,John
2,DDDD,Jain

Cheers

[arun.v7]

I will list down the specifics. The first column is a group number which will always be a numeric value ranging from 1 to 6. The second column can be any string but there can be only two unique values for each group according to the first column. Only one value can repeat in more than one group and that value is fixed. For example if we assume that BBBB is the value which repeats in each group(it can repeat doesnt mean it will repeat, it may happen that it might not be present in any group), then there will only be one other string in group one, but that string can repeat any number of time according to the group strength.

[arun.v7]

the scenario you told will not happen since there can only be two unique values for a single group of first column. i.e, in 1,1,1 there can only be two unique values in second column. same with 2,2,2

[sudster]

In your sample answer, you have Arun and Ashwin sorted wrongly!

[arun.v7]

Thank you so much for checking.
In your code i could see that there’s some sorting according to the column “Name”. But i dont want to sort the data based on the column “Name”. I want the rows having the same “JMBWN” value to be together and the first column to be in order.
I got the result from your code, but does this solution satisfy the above criteria even if values in “Name” column changes?