How to filter Excel column with part of the value

[stick3r]

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 ?

 

[FrancescoDiMuro]

@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

[stick3r]

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 September 30, 2018 by stick3r

[water]

Are the values stored as text or numbers in Excel?

[stick3r]

At the beginning it was General, but I have tried Text and Numbers now, still the same empty filtered excel.

[FrancescoDiMuro]

@stick3r
A possible solution:

expandcollapse popup

#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 September 30, 2018 by FrancescoDiMuro

[water]

Did you have a look at _Excel_FilterSet?

[stick3r]

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.

[water]

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

Edited September 30, 2018 by water

[FrancescoDiMuro]

@water

Shouldn't be

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

And not

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

? 

Cheers

[water]

Should be 

$oWorkbook.ActiveSheet

or

$oExcel.ActiveSheet

 

[FrancescoDiMuro]

@water

Thanks for the tip!

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