Filter out distinct coloumn data

Help Studio
, ,
1

Hi Community,

image
image668×535 116 KB

From the above screen shot i need to filter distinct data from coloumn name ‘Audit Area description’ and ‘fiscal year’ but not to apply filter on the coloumn amount, i need the output datatable like below the ‘required data’ that i have attached in the screenshot for the reference i have attached a sample excel sheet below in which in sheet 1 data table operation need to be applied and in sheet 2 is the desired output i need, please help me to find the solution
Regards,
Alan
Demo.xlsx (9.2 KB)

2

@alan.prakash

(From row In dtInput
            Group row By key = New With { Key .AuditAreaDescription = row.Field(Of String)("Audit Area Description"), Key .FiscalYear = row.Field(Of String)("fiscal_year")}
            Into Group
            Select Group.First).CopyToDataTable

image
image726×450 26.6 KB

Please try this if this not works please update

3

Not working giving output like this
image
This grouping function i have tried before but not worked

4

Hi @alan.prakash

You can achieve this using the Invoke VBA, Save the attached file in your project folder & invoke it like this.

image
image515×595 19.9 KB

Hope this helps :slight_smile:
Code_DataFilter.txt (1.9 KB)

5

@alan.prakash

The Sheet1 varies sheet2 sheet1 contains 3 columns and sheet2 contains 4 columns please specify your requirement

6

Hi thanks for the reply, actually i am getting this in json array format and then i have converted that to datatable that datatable values that i have attached on the excel sheet if we invoke vba will it work? and also this data differs like coloumn name should be same but data differs, i have seen your code there in the fiscal year coloumn data may change some time more year will be added like FY 2027,2028 etc for this case maybe it will work but i need more dynamic way

7

actually my desired output is in sheet 2, i need datas like in sheet 2

8

Hi @alan.prakash

Please refer the attached screenshot and the xaml files:
xaml:
Sequence2.xaml (9.7 KB)

Excel:
Demo.xlsx (9.4 KB)

image
image880×596 32.5 KB

Regards

9

Can we make this more dynamic

10

Hi @alan.prakash

Copy the datatable values to any excel then the below script will work

You can use this code instead this will adapt even if we have more Years like FY 2027
Code_DataFilter.txt (2.2 KB)

Tested it is working

image
image859×262 6.74 KB

Hope this helps :slight_smile:

1 Like