MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Highlight All Matching Names In Both the Master and Absentee Lists?

I have a table with a master list of names. In another table there is a list of absentees. When someone in the master list notifies us they will be absent we put their name is in the absentee list. So, the absentee list is a subset of the master list. I want to highlight the matching name in both the master list and the absentee list? By highlight I mean changing the cell or text color, or any means of having the same name in both the master and absentee lists stand out.

I’m doing this because each week the Numbers document is distributed to a team of coaches who assign bowling lanes to disabled bowlers on the master list. An absent bowler should not be assigned a lane. Lane assignments are made before bowling time so coaches can expedite preparing disabled bowlers for play.
—–
Gregg Dooley

Comments: One Response to “How Do I Highlight All Matching Names In Both the Master and Absentee Lists?”

    8 months ago

    So conditional highlighting wont work for this for a variety of reasons. But you can make the row stand out.

    To mark someone in list A who is in list B, use MATCH to find the name in B, and then ISERROR to convert that to a true or false value, and then IF to convert that to some text you can show. So if the table "Master" has the names in column B, and the table "Absent" has the name in column B, then in column C (cell C2 in this example) of Master have this:

    IF(ISERROR(MATCH(B2,Absent::B,0)),"","X")

    So MATCH tries to look at the name in B2. If it doesn't find it then it returns an error which makes ISERROR true and then "" (blank) is used in C2. If it does find it than MATCH is a number, ISERROR is false and the IF will return "X" to appear in C2.

    Replace "X" with something better. Like "(Absent)" or "šŸš«" or whatever you like.

    As for also doing something in the Absent table, I'm not sure what you mean. The only reason someone would be in the Absent table is if you put them there. So why mark them in addition to that? Wouldn't that just be marking each and every one?

    Also, couldn't you just have a checkbox in column C of the Master list and have that column represent "Absent." Why the need for a second table at all?

Leave a New Comment Related to "How Do I Highlight All Matching Names In Both the Master and Absentee Lists?"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)