Jump to content

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 ?

 

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

ALWAYS GOOD TO READ:

 

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.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 (2021-07-20 - Version 1.0.0.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

 

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

ALWAYS GOOD TO READ:

 

Link to post
Share on other sites

Did you have a look at _Excel_FilterSet?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.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 (2021-07-20 - Version 1.0.0.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

 

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.

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 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.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 (2021-07-20 - Version 1.0.0.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

 

Link to post
Share on other sites

Should be 

$oWorkbook.ActiveSheet

or

$oExcel.ActiveSheet

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.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 (2021-07-20 - Version 1.0.0.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

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...