Filtering non-numeric data

Copper Contributor

I have a table that is all words (specifically, it's a table listing typeface names), and I'd like to be able to filter it somehow based on custom properties I've assigned them. I can't figure out how to do this, because I'm not working with numbers or just sorting alphabetically. E.g., I'd like to tag fonts based on the languages they support - English, Vietnamese, Korean, etc. - or based on availability of other features like weight or italics, and then be able to filter the table using those tags. Is something like this possible in Excel, and if so, how do I do it? If not, is there a different program I could use to do this?

I'm working on a Windows 11 desktop using Excel for Microsoft 365, version 2401.

2 Replies

@mawalz21 

The core of the answer is to compare the properties of the entire list of fonts against the requirement to obtain an array of TRUE/FALSE.  These criteria are multiplied to give the combined AND condition used to filter the names.  As a simple worksheet formula

= FILTER(name, (prop="Proportional") * (serif = "NonSerif"), "Not available")

Using LET and LAMBDA to separate the user input from the calculation

= LAMBDA(p,s,
    LET(
      criterion1,  prop = p,
      criterion2,  serif = s,
      FILTER(name, criterion1 * criterion2, "Not available"))
  )("Proportional", "nonSerif")

Finally, hiding the 'workings' within a Named Lambda function

= FontFinder("NonProportional", )

"where"

FontFinder
= LAMBDA([p],[s],
    LET(
      criterion1, IF(ISOMITTED(p), TRUE, prop=p),
      criterion2, IF(ISOMITTED(s), TRUE, serif=s),
      FILTER(name, criterion1 * criterion2, "Not available")
    )
  )

Get off wherever you feel comfortable, but the key is to start the journey.

@mawalz21 

If your data used a separate column for each language of interest, this would be a trivial issue—you could use Excel's Auto Filter capabilities.


But let's assume that you want to use a delimited list of language identifiers (names or codes or tags). In the attached workbook, I use comma-delimited lists of the ISO 639 language codes, but the solution is the same for essentially any language identifiers; they can be of variable length, but cannot be case-sensitive or include commas or whatever delimiter you choose.


See the attached workbook.  Note that my language support lists are neither complete nor necessarily accurate.

 

 

Comic Sans and Helvetica walk into a bar, and ask for drinks.  The bartender glares at them and says, "We don't serve your type in here."