Jump to content
Sign in to follow this  
stick3r

How to filter Excel column with part of the value

Recommended Posts

Hi,

I have excel with 15k rows and I need to filter it. The problem is, that I have 5 digits number and I need to find all values starting with that 5 digits.

E.g. I have 33333, and I need to find all values starting with 33333. It could be 3333311, 3333322, 3333369, 3333399.......

$oExcel.ActiveSheet.Columns("A").AutoFilter(1, "33333", $xlFilterValues)

This line does not work, as it looks exactly for "33333".

What is the best way to get all values starting with 33333 from Column A ?

 

Share this post


Link to post
Share on other sites

@stick3r

This guide seems to be very helpful :)

Try to change your code with

$oExcel.ActiveSheet.Columns("A").AutoFilter(1, "33333*", $xlFilterValues)

The asterisk is used as wildcard ( as it is in many other languages ), to describe the operation as "Match everything that starts with 33333".

In your case, you were searching exactly for the number 33333.

You can use wildcards wherever you want...

At the start of your string, in the middle, at the end, both...

Try it and let us know :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

I does not work for me :(

Although when I have a mouse on filtered A1, it says Begin with 33333, but all rows in excel are empty like there would be nothing to show with this 33333* criteria

Edited by stick3r

Share this post


Link to post
Share on other sites

Are the values stored as text or numbers in Excel?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

@stick3r
A possible solution:

#include <Array.au3>
#include <Excel.au3>
#include <ExcelConstants.au3>

Global $objExcel, _
       $objWorkbook, _
       $objWorkbook, _
       $strFilePath = @ScriptDir & "\ExcelSample.xlsx", _
       $arrExcelRangeRead, _
       $arrExcelRangeReadFiltered[1]


$objExcel = _Excel_Open(False)
If @error Then
    ConsoleWrite("Error while creating Excel object. Error: " & @error & @CRLF)
    Exit
Else
    $objWorkbook = _Excel_BookOpen($objExcel, $strFilePath)
    If @error Then
        ConsoleWrite("Error while opening the file '" & $strFilePath & "'. Error: " & @error & @CRLF)
    Else

        $arrExcelRangeRead = _Excel_RangeRead($objWorkbook, $objWorkbook.ActiveSheet, $objWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
        If @error Then
            ConsoleWrite("Error while reading the specified range. Error: " & @error & @CRLF)
        Else
            _ArrayDisplay($arrExcelRangeRead, "Rows:")
            For $i = 0 To UBound($arrExcelRangeRead) - 1
                If StringLeft($arrExcelRangeRead[$i], 5) = "33333" Then
                    _ArrayAdd($arrExcelRangeReadFiltered, $arrExcelRangeRead[$i])
                EndIf
            Next
            $arrExcelRangeReadFiltered[0] = UBound($arrExcelRangeReadFiltered)
            _ArrayDisplay($arrExcelRangeReadFiltered, "Rows filtered:")
        EndIf

        _Excel_BookClose($objWorkbook)
        _Excel_Close($objExcel)
    EndIf
EndIf

 

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

Did you have a look at _Excel_FilterSet?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Thanks, @FrancescoDiMuro

This is a possible solution, but it would work better for me to have all columns together with filtered numbers, as there is also important information in other columns I will have to work with.

@water,

 I have found a solution with  _Excel_FilterSet, as $mynumber should always be 5 digits and then 3 extra, so this should find everything I need.

_Excel_FilterSet($oWorkbook, Default, "A1", 1, ">=" & $mynumber & "000", 1, "<=" & $mynumber & "999")

I have found something strange with asterisk code. 

$oExcel.ActiveSheet.Columns("B").AutoFilter(1, "A*", $xlFilterValues)

This actually works on other columns and find everything starting with "A", but not on column A with the numbers. I have tried to copy paste column A to any other, but still it would not filter correctly.

I will try to play with it and maybe I will find the issue, and if you will come up with some ideas, please come back.

Thanks for your help.

Share this post


Link to post
Share on other sites

Usually I define a range as starting:ending column. Could you try „B:B“?

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

@water

Shouldn't be

$objWorksheet.ActiveSheet.Columns("B:B").AutoFilter(...)

And not

$objExcel.ActiveSheet.Columns("B:B").AutoFilter(...)

Cheers :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

Should be 

$oWorkbook.ActiveSheet

or

$oExcel.ActiveSheet

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

@water

Thanks for the tip!

Didn't know that it could be written in both ways :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...