VBA Code to filter a table and all its columns, whilst typing and ignoring accents

I´m studying interpreting and I want to continue to use Excel as my main tool whilst simultaneous interpreting. I`ve already created a FILTER function and I found a simple VBA Code, that filtered one column of my table/glossary as I typed letters and changed the table to show results (I used a TextBox with a linked cell).

The problem I have is that, I want to filter all columns of my multi-language glossary whilst I type and I need Excel to ignore accents (ä,ü,ö,à...).

Just imagine a simple with multiple columns for different terms in different languages.

Any help is deeply appreciated, thank you very much.

As noted before, I used a FILTER function and I am already able to use it to filter all the columns of my glossary, however I think I will need to use VBA to solve the problem of accents (ä,ü,ö,à...) whilst filtering.

FILTER:

=FILTER(Glossary;ISNUMBER(SEARCH(F2;Glossar[Deutsch]))+
         ISNUMBER(SEARCH(F2;Glossary[Englisch]));"no match")

VBA:

Private Sub TextBox1_Change()
    Application.ScreenUpdating = False
    ActiveSheet.ListObjects("Glossar").Range.AutoFilter Field:=2, _
                        Criteria1:= "*" & [B2] & "*", _
                        Operator:=xlFilterValues
    Application.ScreenUpdating = True
End Sub

I know that the VBA Code above just searches the second column, haven`t been able to figure out mulit-column search.

Here's something to try without VBA, using named functions:

• substitution method is adopted from Diacritical Character to ASCII Character Mapping

• define the following in Name Manager

Then all2reg can be used anywhere in the workbook - for example enter in F2:

=IFERROR(
    FILTER(
        Glossary,
        MMULT(
            --ISNUMBER(
                SEARCH(all2reg(F2), all2reg(Glossary[[Deutsch]:[French]]))
            ),
            SEQUENCE(COLUMNS(Glossary) - 1, , 1, 0)
        )
    ),
    "no match"
)

Without using Name Manager

• substitute accented characters in both search term and language columns
• search in all language columns, and get grouped results per row with MMULT function - Microsoft Support

Enter in F2:

=LET(
    acc_1, "ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûüýþÿ",
    acc_2, "ĀāĂ㥹ĆćĈĉĊċČčĎďĐđĒēĔĕĖėĘęĚěĜĝĞğĠġĢģĤĥĦħĨĩĪīĬĭĮįİıĴĵĶķĸĹĺĻļĽľĿŀŁłŃńŅņŇňʼnŊŋŌōŎŏŐőŒœŔŕŖŗŘřŚśŜŝŞşŠšŢţŤťŦŧŨũŪūŬŭŮůŰűŲųŴŵŶŷŸŹźŻżŽžſ",
    acc_chars, acc_1 & acc_2,
    reg_1, "aaaaaaaceeeeiiiienoooooouuuuypsaaaaaaaceeeeiiiienoooooouuuuypy",
    reg_2, "aaaaaaccccccccddddeeeeeeeeeegggggggghhhhiiiiiiiiiijjkkkllllllllllnnnnnnnnnoooooooorrrrrrssssssssttttttuuuuuuuuuuuuwwyyyzzzzzzs",
    reg_chars, reg_1 & reg_2,
    acc2reg, LAMBDA(c, IFERROR(MID(reg_chars, FIND(c, acc_chars), 1), NA())),
    all2reg, LAMBDA(term,
        MAP(
            term,
            LAMBDA(term,
                REDUCE(
                    term,
                    MID(term, SEQUENCE(LEN(term)), 1),
                    LAMBDA(acc, cur, IF(ISNA(acc2reg(cur)), acc, SUBSTITUTE(acc, cur, acc2reg(cur))))
                )
            )
        )
    ),
    IFERROR(
        FILTER(
            Glossary,
            MMULT(
                --ISNUMBER(SEARCH(all2reg(F2), all2reg(Glossary[[Deutsch]:[French]]))),
                SEQUENCE(COLUMNS(Glossary) - 1, , 1, 0)
            )
        ),
        "no match"
    )
)

• adding helper columns as suggested by chris neilsen might improve performance, if your lists are long and many.