How to compare a cell's content against a given set/list/range of values?

Hello

COUNTIF() would be your friend as shown in column B of the following example. Column C shows how to use OR() function if you have particular reason to choose it.

E.g.,

Table 1 (excerpt) A1 code A2 HFU9372 A3 EPD5490 A4 AFS4572 B1 B2 =COUNTIF(CODES::A,A2)>0 B3 =COUNTIF(CODES::A,A3)>0 B4 =COUNTIF(CODES::A,A4)>0 C1 C2 =OR(A2="AFS4572",A2="HFU9372",A2="UWK1993",A2="EPD5490") C3 =OR(A3="AFS4572",A3="HFU9372",A3="UWK1993",A3="EPD5490") C4 =OR(A4="AFS4572",A4="HFU9372",A4="UWK1993",A4="EPD5490")

Notes.

Formula in B2 and C2 can be filled down.

Table is bulit with Numbers v2.

Regards,

H

Hi Mr H,

I concur with Hiroto's suggestion of using COUNTIF, particularly if you want to then show only the rows containing codes listed in the code table.

Here's another example, using the MATCH function. Setup is similar to Hiroto's example:

Note that the numbers in column A of code are there only for the visual reference convenience of the user. They are not used in the matching, and may be omitted if desired.

The MATCH formula is in B2 of the longer table, and filled down column B from there.

B2: =IFERROR(MATCH(A,Code :: $B,0)-1,"")

IFERROR here is used as an error trap. MATCH will throw an error if it is set to find exact matches, and there is no exact match in the list. IFERROR traps that error, and inserts a null string ( "" ) in the cell, making it appear empty.

The colour fill in the cells with match numbers is placed there using conditional formatting (conditional highlighting in Numbers 3). All cells in the column are assigned a set of rules, one for each colour. If the content of the cell is equal to the number in a rule, the fill colour of that cell is set to the colour specified in the rule.

The numbers give an easy means of filtering to show only the rows containing a specific code, as well as the full set possible using TRUE.

If filtering is not required, and you need only a visual highlight, this can be done using conditional formatting/highlighting without the added column in the main table. Up to 15 distinct codes could be highlighted using a different colour for each (in Numbers '09—I don't know if Numbers 3 offers that many colours for highlighting).

No formulas are needed for this method.

Regards,

Barry

Here's how you might do this with Conditional Highlighting in Numbers 3. The advantage of this approach is that you don't need formulas or extra columns. b

Select the cells and click 'Conditional Highlighting':

]

Add a 'Text' rule:

And keep adding other rules as needed:

There appears to be a limit of 15 separate rules. Through the 'Custom Style' (scroll to bottom) you have access to a virtually unlimited number of colors. Of course, if you simply want to highlight all the cells whose value is in a given set of values, you would simply set the same color in each rule.

SG

HI SG,

Essentially the same as the last paragraph and image in my reply above, with the exception that mine placed the values to be highlighted into a separate small table, where they could be more easily edited that those placed as fixed values in the rules themselves.

Thanks for confirming that the full range of colours is available for highlighting in N 3.

Both of our 'highlight only' solutions share two other shortcomings:

—The 15 rule limit is about half the "first 30" mentioned by the OP in the opening post.

—There's no way to 'isolate' (sort or filter) the codes of interest on an attribute of the cell format.

Regards,

Barry

If you find the highlighting approach productive and want to list those codes you want to "match" and "mark" in a separate table you can do this easily in Numbers 3.

With the cells selected as you are adding the rule click the address "lozenge"...

And then click in the relevant cell in Table 2 so that its address appears in the "lozenge" then click the triangle dropdown and click 'Preserve Row'.

SG

There's also an easy script solution that will highlight the entire row of any cell containing one of your target codes:

Just copy-paste the script below into Script Editor (in Applications > Utilities) select the cells in the column containing your codes and click the triangle "run" button.

Add/edit codes as needed in the first line between the {}. Surround each one with quotation marks and separate by commas. Knowledge of AppleScript is not needed.

The advantages: 1) no formulas, 2) no extra columns, 3) highlighting of the entire row, which can be easier on the eyes when working with large tables.

SG

set codesToMark to {"AFS4572", "HFU9372", "UWK1993", "EPD5490"}

tell application "Numbers"

tell front document to tell active sheet

tell (first table whose selection range's class is range)

set selRng to selection range

repeat with c in selRng's cells

if c's value is in codesToMark then ¬

set c's row's text color to "red"

-- or background color to "red" -- etc

end repeat

end tell

end tell

end tell

Hi SG,

Neither of the 'pure' highlighting solutions proposed require either formulas or extra columns. Those requirements were for generating values that could be used to sort or filter the rows matching the listed codes.

For this particular case, I'm surprised by your omission of the largest advantage of using a script here: the ability to handle more than 15 distinct values to trigger the highlighting.

The OP hasn't come back to say if the "about 30" codes involved are a stable group, or a group whose membership is subject to change. If the list will never change, then placing those codes into the script (each enwrapped in double quotes and separated from the next with a comma) is a one-time task. If the list does change, though, each change requires going into the script to edit the list.

That possibility would argue for an extra column table from which the script could retrieve the current list.

Regards,

Barry

Barry wrote:

For this particular case, I'm surprised by your omission of the largest advantage of using a script here: the ability to handle more than 15 distinct values to trigger the highlighting...

... placing those codes into the script (each enwrapped in double quotes and separated from the next with a comma) is a one-time task. If the list does change, though, each change requires going into the script to edit the list.

That possibility would argue for an extra column table from which the script could retrieve the current list.

Hi Barry,

Points well taken. If it's easier to edit the list of codes in a table rather than in Script Editor then a script that reads the list from a table named "Mark These" on the same sheet would be like this:

set codesToMarkTable to "Mark These"

tell application "Numbers"

tell front document to tell active sheet

set codesToMark to tablecodesToMarkTable'scells'svalue

tell (first table whose selection range's class is range)

set selRng to selection range

repeat with c in selRng's cells

if c's value is in codesToMark then ¬

set c's row's text color to "red"

end repeat

end tell

end tell

end tell

As written the table "Mark These" should contain no blank rows.

SG

P.S. Tested with Numbers 3. It may run with Numbers 2 without complaint after changing the third line to:

tell document 1 to tell (sheet 1 whose every table's selection range's class contains range)

MrHuman wrote:

I expect I can modify the "setc'srow'stext colourto "red" " to something that puts a fixed value into the cell instead

Yes, this one will put a "sort code" of 1 in the last column of your table (you would of course want to set up that column first). If you want the code in the second column just change:

set c's row's last cell's value to myMarker

to:

set c's row's second cell's value to myMarker

etc.

SG

set codesToMarkTable to "Mark These"

set myMarker to 1

tell application "Numbers"

tell front document to tell active sheet

set codesToMark to tablecodesToMarkTable'scells'svalue

tell (first table whose selection range's class is range)

set selRng to selection range

repeat with c in selRng's cells

if c's value is in codesToMark then

--set c's row's text color to "red" --uncomment this if want red, etc.

set c's row's last cell's value to myMarker

end if

end repeat

end tell

end tell

end tell

Hi Jeremy,

You wrote (in reply to Hiroto):

I didn't use the 'COUNTIF' option because I couldn't understand why your tables had HFU9372, AFS4572 and EPD5490 showing up as both TRUE and FALSE in your results. I needed all instances of [the 30 primary alphanumeric codes] showing as TRUE only, so I could sort them all together. If you wanted to elaborate I will pay attention!

I don't understand what you're saying here. Hiroto's results (as shown in his post) are exactly the same in both test columns of his table. Both examples use the same table.

The four values in rows 2, 3, 4 and 8 of column A of Table 1 match the four search values in the Code table and are marked TRUE in column B, containing his COUNTIF formula. The values in rows 5, 6, 7 and 9 of table 1 each differ in their last digit from the closest match in the Code table, and are marked FALSE.

The four values in rows 2, 3, 4 and 8 of column A of Table 1 also match the four values in the OR formula and are marked TRUE in column C, containing this COUNTIF formula. The values in rows 5, 6, 7 and 9 of table 1 each differ in their last digit from the closest match in the OR formula, and are marked FALSE.

Of the two formulas providing a TRUE/FALSE, the COUNTIF version, with a separate table to hold the codes would still be my choice. The OR version, with a comparison for each code, will get very unwieldy (and difficult to edit) with 'about 30' comparisons to write into it.

A formula providing a TRUE/FALSE result will allow filtering and sorting into a single 'lump' containing all the marked code lines. A formula providing a distinct numerical result (such as the MATCH version in one of my posts) permits a sort that will group the identical codes together and allow filtering to display only those results.

Regards,

Barry