Grouping Data on Excel Based on Data and Crosscheck Data

Help Activities
, ,
1

image
I want to group data based on the Collection_No and want to do further work on them like, crosschecking with a similar excel sheet, providing flag mismatched entry.

How may I perform this action?

2
3

HI,

In this case, I recommend to use Dictionary(Of String, DataTable) as the following, for example.

image
image952×805 32.7 KB 

dict = dt.AsEnumerable.GroupBy(Function(r) r("Collection_No").ToString).ToDictionary(Function(g) g.Key,Function(g) g.CopyToDataTable)

Sequence3.xaml (7.6 KB)

Regards,

4

@Yoichi thank you but can you please help me to generate a output from the following xaml?

I want to print it in the console initially, then I will write on excel

5

sharing the sample excel here
sample_excel.xlsx (11.6 KB)

6

Hi,

How about the following?

image
image923×1158 31.6 KB

Sample20230124-7L.zip (11.8 KB)

Regards,

1 Like
7

thank you @Yoichi

this is almost exactly how I want to get things but I want the Collection_No’s as individual datatable rather generating them as individual excel file.

how may I get them as individual and dynamic datatable, can you please help me with that?

image

8

I think it’s difficult because we cannot make variable name dynamic. Is there any problem if use Dictionary? In most case, it will work.

Regards,

9

Assign Activity:
TableList | DataType: List(of DataTable) =

(From d in YourDataTableVar.asEnumerable
Group d by k=d("Collection_No").toString.Trim into grp=Group
Select t = grp.CopyToDataTable).toList

As similar
Assign Activity:
TableList | DataType: List(of DataTable) =

 YourDataTableVar.asEnumerable.GroupBy(Function (x) x("Collection_No").toString.Trim).Select(Function (g) g.CopyToDataTable).toList
11

image
if I can store this value in a datatable with the same column name of the excel file, then it will be easier for me to implement my process

@Yoichi

12

@ppr

can you help designing me this assign activities in this xaml?
Sample20230124-7L.zip (11.8 KB)

13

In general you are well supported by @Yoichi and we would recommend to stay at one approach and bring it to the final solution.

For the last step just redefine your exact requirements for the expected and output and we will help for this.

  • The dictionary approach from Yoichi allows you also to retrieve all seperated datatables as Datatable: YourDictVar.Values.ToList

  • You can implement as mentioned above as the table list

  • And also you can set a table name to the Datatable and reflect with this the group key

As you see there are many variations. All is now about fixing the requirement and needs

1 Like
14

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