Grouping and eliminating excel columns

Good day,

It would be helpful if someone could share their 2 cents on how to handle this scenario.
Below is a sample of data that I have to process:
image

I have to eliminate the duplicates and group the record based on the Node and Description with its highest priority.
I would need the below result:

image

Are there functions/activities that I can use for this scenario?

Thank you,
Meya

@Meyammai - Please check this…

 (From d In Dtinput.AsEnumerable
 Group d By k=d(0).toString.Trim, k2=d(1).toString.trim,k3=d(2).toString.trim Into grp=Group
 Let ra = New Object(){k,k2,k3}
 Select dtGrouped.Rows.Add(ra)).CopyToDataTable
1 Like

Hi @Meyammai
You can also try this:
convert your excel as Datatable using Read range activities,
Later you can use Remove Duplicates row activity and Sort Datatable Activity.

@Meyammai Check this below link,

Hope this may help you :slight_smile:

@Meyammai
your case is about grouping and custom sort

give a try on following:
create a dictionary string, int3 and store the Prio Values along with its sort rank
dictCustomOrder =
new dictionary(Of String, Int32) From {
{“High”, 1},
{“Medium”, 2},
{“Low”,3}
}

Use an assign activity
left side: dtResult | Datatype: DataTable
right side:
(From d In dtData.AsEnumerable
Group d By k1=d(“Node”).toString.Trim, k2=d(“Description”).toString.Trim Into grp=Group
Let go = grp.OrderBy(Function (x) dictCustomOrder(x(“Priority”).toString.Trim))
Select go.First()).CopyToDataTable

1 Like

Hey,

Is it possible to show a screenshot of what you are describing?
I have not used dictionary activities before and find it a bit hard to follow.

Thank you in advance!

Variables:

Flow:
grafik
grafik

Statements taken from above

Find starter help here:
GroupBy_2Col_GRPCustomSort_TakeFirst.xaml (8.5 KB)

2 Likes

Thank you so much @ppr !
It works just as I was expecting.

Thank you!!

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