Excel filteration

Hi All,

Could anyone help me with the below challenge I am undergoing in my project.

I have a excel sheet, which has the Column called “transid”. It has the four kinds of pattern data in it(eg: 111,1A1,A11,1AA). Now, I want to filter the data based up on the pattern and move them into different files.

Looking for a solution.

Regards,
Rahul

  1. row(“transid”).Contains(“A”) = False — 111
    In true
  2. row(“transid”).StartsWith(“A”) ---- A11
  3. row(“transid”).EndsWith(“A”)—1AA
    4)Else —1A1

Thank you @vvaidya.

But, what if It contains “B”. I meant “A” may be any alphabet.

Could you please attach the xaml if possible.

Regards,
Rahul

Just to make sure, you are reading the pattern into a data table and filtering, correct?

Yes, you are correct.

filter.xaml (7.1 KB)

1 Like

Thank you @vvaidya It worked

Just a question how do you make your excel document into a data table?

This Activity, there are many examples in this forum.

This might be a better solution, using Pattern and Lambda/LINQ to create your filtered datatable. :slight_smile:

dtVar.AsEnumerable().Where(Function(row) row(“transid”).ToString=System.Text.RegularExpressions.Regex.Match(row(“transid”).ToString,“[0-9][A-Z]{2}”).Value or row(“transid”).ToString=System.Text.RegularExpressions.Regex.Match(row(“transid”).ToString,“[0-9][A-Z][0-9]”).Value or row(“transid”).ToString=System.Text.RegularExpressions.Regex.Match(row(“transid”).ToString,“[A-Z][A-Z][0-9]”).Value or row(“transid”).ToString=System.Text.RegularExpressions.Regex.Match(row(“transid”).ToString,“[0-9]{3}”).Value).CopyToDatatable

or change .CopyToDatatable to .ToArray

This line should filter down to the rows that meet the criterias of 3 numbers, 2 numbers and a letter, or 1 number and 2 letters. Of course there is lot more you can do with this, and feel free to correct this or add to it.

EDIT: Oh also, if you want to move each criteria to a different file, then remove the "or"s and create a line for each filtered criteria that you want.

Thanks.