Excel Filter Question

[jntrk]

Hey everyone,

I have an excel document with alot of rows and columns, let me first start by saying that for each row is not an option.

In this excel document there is a column named “percent” values are given as “not given”,
“>±50%” , “>±200%” , “>±10%” etc.

I want to get all the rows where percent value is bigger than 30 so “>±30%” and more, meaning I dont want to get “>±10%” then write filtered dt to another excel.

Any ideas?

[ppr (Peter | MVP)]

@jntrk
I am travelling and will have a Look on later again. The Main Idea ist to extracz the Numbers Part with a Help of regex. The Pattern would be \d+

The extracted Numbers can be Used for the Filter criteria.

Lets assume system.text.regularexpressions is imported. A linq could Go in following Direktion

(From d in dt.asenumerable
Let sv= regex.match(d(yourcolnameorindex). toString,\d+).toString
Let iv=CInt(sv)
Where iv>=30
select d).copytodatable

Just keep in mind copytodatatable fails for empty result. But WE do have a pattern to make Note defensive

Have for this a Look Here

Filter Datatable rows on yesterday's date - large excel

First step about generating dates is just for demo purpose. You will use your datatable e.g. from read range Excel. [grafik] filtering the datatable With the help of LINQ - returning the filtered rows as a list If result is not empty then copytodatatable else create empty datatble by cloning origin datatable LINQ Statement: (From d In dtData.AsEnumerable iterate over all rows, reference looped row with d Where now.AddDays(-1).Date = dateTime.ParseExact(d(0).toString,“dd/MM/yyyy”, Cultur…

[jntrk]

how can I extract the number part without for each row then read cell?

So what I understand from your solution is, extract all the values then regex to get the number part, and linq to filter for getting the result datatable, which I think can work but how can I extract the values in single execution and apply regex for all?

[ppr (Peter | MVP)]

@jntrk
For the learning purpose I would suggest to Run the linq / Code Just to Explore and inspecting the result.

About your question I am Not Sure If I got you in total. Let US Balance Out the technics and your expectations. Regardless If IT is a oneLiner or Not some how all rows are to Check for filtering. So IT IS Looping internally anyhow.

May we ask you just to resharp your question and help us to understand it better. Thanks

[jntrk]

@ppr

(From d in dt.asenumerable
Let sv= regex.match(d(yourcolnameorindex). toString,\d+).toString
Let iv=CInt(sv)
Where iv>=30
select d).copytodatable

Okay let me clear myself then.
Yes I understood when using linq its looping internally I’ve read the link u send.
I understand what you are trying to do with the linq, its filtering the rows where number>30. What I didnt get is.

Let sv= regex.match(d(“Number”). toString,\d+).toString , I didnt quite understand here.
And I get this error trying to use this linq

Is regex.match causing the problem?
How can I work with this?

[ppr (Peter | MVP)]

@jntrk

Let sv= regex.match(d(“Number”). toString,\d+).toString , I didnt quite understand here

The let Statement alows to memorize a Part result for later use. Here WE extract the Numbers Part.

About the issue i will have later a Look on IT once i am Back on Laptop. The Statement was blindly written on Cellphone so Not surprising that some smaller issues occurs.

[jntrk]

Okay then we assign it to iv and if its more than 30 we select that value and copytodatatable, makes sense.

I didnt know you were helping me from your cell , thank you very much you are a legend.
Dont waste your time on your phone to do it. Let me know when you are available from your laptop, again thank you .

[ppr (Peter | MVP)]

@jntrk
Give a try on following. The regex pattern Argument Type ia String. The “” was fortgotten. Change IT to

Let sv= regex.match(d(yourcolnameorindex). toString,"\d+").toString

[jntrk]

I get Match is not a member of String.
What data type should regex be? And where does regex value come from, I just created a variable named regex but its empty.

[ppr (Peter | MVP)]

@ppr

system.text.regularexpressions is imported.

Then regex.match is recognized

Regex.Match Method (System.Text.RegularExpressions)

[jerry_maguire]

1. Filter a range of data
2. Select any cell within the range.
3. Select Data > Filter.
4. Select the column header arrow.
5. Select Text Filters or Number Filters, and then select a comparison, like Between.
6. Enter the filter criteria and select OK.

Let me know if this helps.

Regards,
Jerry

[ppr (Peter | MVP)]

Regex - extract sum from text

it can be used within the Match activity or within an assign: left side: strVal right side: Regex.Match(YourStringVar, “(?<=xyz).*(?=abc)”).Value ensure following: [grafik] Also have a look here:

Refer to the Screenshot for the import also Take a Note of the Link to the regex megapost