Filter out distinct coloumn data

Hi Community,

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
Demo.xlsx (9.2 KB)


(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

Please try this if this not works please update

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

Hi @alan.prakash

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

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


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

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

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

Hi @alan.prakash

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

Demo.xlsx (9.4 KB)


Can we make this more dynamic

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

Hope this helps :slight_smile:

1 Like