Automation problem: Need to order a Data table or an excel with some requeriments

[Pelayo_Celaya_Fernandez (Pelayo Celaya Fernández)]

Hi everyone,

I need to order a Data Table if possible, if not I should not to be a big problem to do it directly in excel with the next logic:

Context: Imagin a Data Table with only two columns lets call it: C_One and C_Two

C_One has the type of the element, in this example it will be numbers so we will have four types: 1, 2, 3 and 4. This means that all the elements in the table will have in C_One one of those numbers.

C_Two is a string descriptor.

What I need to do is to order ascending by C_One but every 2 rows with the same type/number change to the next so if I have this:

Have to say that there are many more types in real case and number of items each type has is not equal and not even and there are like a thousand or more per run.

Thank you in advance,

[ppr (Peter Preuss)]

maybe the sample is too generic / not complete specifying alternate scenarios (e.g. C1 groups do have different lengths

We can do:

• Grouping / ordering (dict Approach)

Assign Activity:
dictOrderedGroups | Dictionary(Of String, List(Of DataRow)) =

(From d in dtData.asEnumerable
Group d by k=Cint(d("C_One").toString.Trim) into grp=Group
Order by k
Select t = Tuple.Create(k.toString, grp.ToList)).ToDictionary(Function (t) t.Item1, Function (t) t.Item2)

• Prepare empty target datatable
  dtResult = dtData.Clone

Now we can use a nested loop:

• Dynamic Looping over the chunks:1-2,3-4 …
  • Dynamic grabbing from all groups the outer looped chunk
  • if there is a chunk for the group, add it to dtResult

Let us know if you need further assistance

[ppr (Peter Preuss)]

A LINQ Approach

Variables:

Flow:

Code:
dictOrderedGroups =

(From d In dtData.asEnumerable
Group d By k=CInt(d("C_One").toString.Trim) Into grp=Group
Order By k
Select t = Tuple.Create(k.toString, grp.ToList)).ToDictionary(Function (t) t.Item1, Function (t) t.Item2)

MaxLength = dictOrderedGroups.Max(Function (x) x.Value.Count)
SegmentCount = Cint(Math.Ceiling(MaxLength/ChunkSize))

dtResult =

(From s In Enumerable.Range(0, SegmentCount)
From v In dictOrderedGroups.Values
From cr In v.Skip(s*ChunkSize).Take(ChunkSize)
Select r=cr).CopyToDataTable

Crosscheck Input / Output

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