Mac Numbers Categorize based on partial match

I just saw that! I put it in as a feature request yesterday but apparently it was already finished and ready to distribute. It makes my method (and a number of other problems) a lot easier. Here it is now:

Last row of Table 2 is a footer row. It can be hidden after everything is set up.

Formula in that row is =TEXTJOIN("|",1,A)

Formula in cells of Table 1::E is =IFERROR(XLOOKUP(REGEX.EXTRACT(LOWER(C),LOWER(Table 2::B$19)),Table 2::A,Table 2::B),"")

For those who want to know how it works,

The textjoined string is a "regular expression" with the "OR" character between each substring. When used with REGEX.EXTRACT on the longer string in Table 1::C, REGEX.EXTRACT will try to "extract" one of those substrings from the longer string. The result of REGEX.EXTRACT will be one of the substrings (or an error if none are found). That substring is then used in XLOOKUP to search Table 2::A and return the category from Table 2::B.

The LOWER function is used in the formula because REGEX.EXTRACT is case sensitive. It ensures all characters being compared are lowercase. IFERROR is for when no substring can be "extracted" which can happen if there is no entry in Table 1::C or if there is no matching substring in Table 2::A.

I tested it with 2400 entries in Table 1 and 200 entries in Table 2 with no noticeable slowdown.

Skylas wrote:

a way to either make it work in Numbers or achieve the same result by using a different formula.

Yes, you can do this in Numbers by building your reference categories and search text for partial matches right into the formula, something like this.

I used option-return in the formula editor to separate each search text-category pair onto its own separate line. That makes the formula easier to edit.

Each pair takes this form:

COUNTMATCHES(reference to cell with the description text,"the search text"),"the associated category",

To add a new pair just add a line before the line with TRUE at at the end. Be sure to include the trailing comma followed by option-return.

The formula above is like this (unfortunately the forum software doesn't like option-returns but you can add them yourself at the appropriate places as shown in the screenshot):

=IFS(COUNTMATCHES(A2,"John Food"),"Food",COUNTMATCHES(A2,"Home Depot"),"Hardware",COUNTMATCHES(A2,"Storage Depot"),"Storage",COUNTMATCHES(A2,"Burger King"),"Food",COUNTMATCHES(A2,"Amazon"),"Other",TRUE,"")

COUNTMATCHES here acts like a "contains" operator.

I haven't run into a practical limit to the number of lines of pairs in the formula. This is not quite as easy as simply typing search terms and category names into ranges of cells. But simple enough to be practical.

SG

There was a recent thread like this. The Excel formula you posted is an "array" formula. Array formulas can do in one cell what requires a column (array) of calculations in Numbers. In Numbers, while it is easy to find a substring in column of longer strings, it is not so easy to match a long string to a column of substrings. You can find "ford" in a column of strings like "xyz ford pdq" but not the other way around.

Below is the link to the other thread. See if it makes any sense. It is the exact same problem, just different columns/tables. If not I'll rework it for you.

Numbers - IF Contains Specific Text then … - Apple Community

EDIT: I see SGIII posted while I was writing. His solution was also in that other thread.

Hi Skylas,

Some thoughts;

• click in any spare cell and type = to bring up the Function Browser. Type search to see this:

wild cards are allowed. Scroll down to see some examples.

• Take a look at Template Chooser > Personal Finance > Personal Budget for examples of using Categories.

We can adapt that Personal Budget to allow it to choose partial matches.

• Please show us some of your examples where you want partial matches from transactions.

Regards,

Ian.

"becomes quite cumbersome having to edit the nested if formulas"

Please note that there are NO "nested IF" functions here.

This approaches use the IFS function. The IFS function eliminates nesting. It enables simple addiction, subtraction, or modification of search term-category pairs, just as you would do with the cell ranges.

It's not quite as convenient as having ranges of cells as in Numbers. But not hard at all. I suggest trying it before rejecting it. 😀 It works very well here.

And no clutter of extra tables and such.

SG

Using IFS is MUCH easier than nested IFs, which are a nightmare.

Yes, as you say, the formula must be filled down again after a modification. For me that takes a couple of seconds at most. (And of course the filling often has to be done anyway after a new import.)

I use this approach with about a dozen, occasionally changing, categories and search terms. Really not much trouble.

On the Mac, with the formula editor that allows you to keep the pairs on separate lines, this approach is only marginally less convenient than setting up an Excel array formula and maintaining search terms and category names in separate ranges.

Give it a try!😀

SG

Whichever works best for you.

The IFS formula might get unwieldy if you have a lot of entries. I know on my iMac it gets difficult to edit the formula when the list gets long. It gets long delays. It was getting pretty bad with 80 entries. My solution may also slow down, though, I didn't test it for that.

I hope one day we get Excel's TEXTJOIN function which would eliminate the need for that column of concatenations in my solution. TEXTJOIN can concatenate an entire range, with a delimiter between each item. Of course, you could do CONCATENATE(C2,"|",C3,"|",C4,"|",C5,"|",...,C100) but that is painful and would get mixed up if you sorted the table.

Skylas wrote:

can imagine it would get pretty sluggish running the IFS solution over say 500 transactions...

Each IFS refers to only one transaction. The number of transactions is not relevant.

If you have a huge number of search term-category name pairs then there might be some sluggishness. On my machine it is not sluggish.

Might want to actually give it a try!😀

SG