Excel manipulation to get top 15 from data

Help IT Automation
,
1

Hi team, i have a raw data here i have to get top 15 vendors by the item count value, how can this be done

2

Hi! Welcome to the Community!

Please provide the some more info.

  1. Item Count value is your ColumnName?

if yes. You can achieve this by using sort DataTable.

Provide the ColumnName or ColumnName Sort-Descending.

To get the top 15 Records

  1. Use for each row in DataTable provide the SortDt
  2. Take one if Condition like this CurrentRow.Count<=15

Regards,
NaNi

3

Assign activity
arrVendors | String( ) - StringArray =

YourDataTableVar.AsEnumerable.Select(Function (x) x(YourColNameOrIndex).toString.Trim).Distinct().Take(15).toArray
4

thanks, but item number is a column with the help of the count of that column i need to sort it

5

could it be the case that following scenario is to handle (if not then please share your input sample, processing description and expected output with us=

VendorA
VendorA
VendorA
VendorB
VendorC
VendorB
…

As VendorA occurs 3 times he is first, Vendor B 2 times is second…

Assign Activity:
arrTopVendors | String( ) - String Array =

(From x in YourDataTableVar.AsEnumerable.Select(Function (d) d(YourColNameOrIndex).toString.Trim)
Group x by k=x into grp=Group
Order by grp.Count
Select v=k).Take(15).toArray
6

lets consider there are 5 vendors VEndor A,B,C,D,E . Here the case is vendor A has the count of 100 in the part number column, next lets say Vendor C has the count of 90 in the part number column like this it is, here i need the top 15 based on this

7

as mentioned, we prefer detailed inputs / sample data. However as far your description has been understood, above the solution has been shared.

8

when dealing with 2 columns: vendor name and count, then sort on column name and take the first 15 vendors (take statement)

9

yeah i tried sorting it down, but not able to do it. should sort vendor name column with respect to item number column(count of it), thats it. but i m not able to achieve it

10

Hello @Abhilash_K

Can you share a sample dataset? It would be easy if you do that.

Share the input data and the expected output.

11

Book1.xlsx (9.4 KB)
sheet 1 will be the input and sheet 2 will be the expected output

12

Book1.xlsx (9.4 KB)
sheet 1 will be the input and sheet 2 will be the expected results

13

implementing your case ( we do refer to the inital data column structure, vendor name, item # count

Assign Activity:
dtResult = YourOriginDataTableVar.Clone

Assign activity
dtResult =

(From d in YourOriginDataTableVar.AsEnumerable
Group d by k=d("vendor name").toString.Trim into grp=Group
Order by grp.Count DESCENDING
Let ra = new Object(){k, grp.Count}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

Kindly note:

  • your example excel differs from requested title case (no fetch of top 15)
    Input:
    grafik
    Output
    grafik
    last output row moto|1 looks like a copy paste issue and we ignored it for the requirement understanding
14

ok it can be top 3

15

for selecting the top x rows we can do:

(From d in YourOriginDataTableVar.AsEnumerable
Group d by k=d("vendor name").toString.Trim into grp=Group
Order by grp.Count DESCENDING
Let ra = new Object(){k, grp.Count}
Select r=dtResult.Rows.Add(ra)).Take(3).CopyToDataTable
16

Book1.xlsx (9.4 KB)
sheet 1 is input and sheet 2 is expected results, how can this be achieved