Help to create a soluton on filtering and sorting the excel

Help Something Else
1

Hi i have a question regarding excel Automation
i have an excel table as below

image
image459×562 16.1 KB

then based on column “Code” i will create a separate tab/sheet for each similar “Code”
then i will fil in the data accordingly based on the Code and the sheet
but i also need to tab/sheet with a rules like this numbers 1-9 then 0

output will be like this.

image
image573×504 10.7 KB

image
image561×565 11.7 KB

thank you

2

@StevenIsRobotOnline

hi

refer this

arr_sheetnames=dt2_Updated.AsEnumerable.Select(Function(r) r(1).ToString).Distinct.toarray

arr_sheetnames is the array of string type

arr_datatables=dt2_Updated.AsEnumerable.GroupBy(Function(r) r(1).ToString).Select(Function(grp) grp.CopyToDataTable).toarray

arr_datatables is the array of type datatables

Sequence.xaml (9.8 KB)

cheers

1 Like
3

image
image425×665 21.9 KB

arr_String(SheetNames)=dt_Sample.AsEnumerable.Select(Function(r) r(“Code”).ToString.Trim ).Distinct.ToArray

Build data table will be dt_temp

image
image742×518 8.01 KB

dt_temp(Data to write on each sheet based on code column)=dt_Sample.AsEnumerable.where(Function(x) x(“Code”).ToString.Trim.Equals(currentText.ToString.Trim)).CopyToDataTable

You can refer to the below given xaml if required
dummy.xaml (14.1 KB)

4

Hi @Unnathi_Bhat ,
You can use value
=RIGHT(CELL(“filename”,A2),LEN(CELL(“filename”,A2))-FIND(“]”,CELL(“filename”,A2)))

image

regards,

5

may i know where the sorting function take place?

6

Sorting of rows based on code is done at dt_temp=
dt_Sample.AsEnumerable.where(Function(x) x(“Code”).ToString.Trim.Equals(currentText.ToString.Trim)).CopyToDataTable
here currentText is the each unique code which is stored in the array

7

but i dont think it will fit my sorting requirement

8

@StevenIsRobotOnline

have you tried the code which i have shared

9

hi i havent but i also didnt see any sorting from your code?

10

@StevenIsRobotOnline

i have got the expected output which you want try to check it once

11

how do you want the rows to be sorted?can you elobarate
according to my understanding it shoud read from an excel sheet based on unique reference in column code divide it into multiple sheet
next the data will be sorted based on that

12

ok thank you , i will cehck it