How to search a spreadsheet for a name?

As far as I know this feature is unavailable.

It's a task tor the "Find" feature.
It would be faster than an AppleScript to scan several tables.

Yvan KOENIG (from FRANCE dimanche 22 février 2009 19:04:24)

This may not suit your needs - I assume you want to enter a name to your data and get a warning if it already exists, leaving it there if it doesn't. As pointed out, the Find command will find matches, but who wants to go through a series of those with multiple tables.

I MAY have at least figured out a way to simply it, though probably not what you hoped for.

You can use "Countif" like this:

=COUNTIF(C10:H236,A2)

The above is a table I already have with lots of names in it. I put that formula in A1. In cell A2, I typed a name I new existed (apparently it doesn't matter if it is upper or lower case). C10:H236 is the range to search.

I figure you could do this same formula repeated for references to each table you have, either each in a different cell or combine them into on longer formula with "+" adding them together.

So, suppose you want to add a name. Instead of entering it in the normal data section, you enter it in a particular cell you are using as your reference.

The formula cell will tell you if it exists already, without having to go through the Find procedure. If you have one long formula including all tables, it won't tell you which one it appears in, but you'll know it exists someplace.

If you create separate formula cells to reference each table, any result that has a number greater than zero would tell you which table it was in.

But your formula cell(s) and cell where you enter the name you are looking for would all be on the same sheet. All would refer to the single cell where you enter the name. To search for a new name, you just keep using the same cell.

Hope that was reasonably clear.

Should have added that the formula won't adjust to a new size in the table. If you add rows or something, you'll have to adjust the formula(s) to check the new size. Or just include lots of extra rows or columns in advance to handle everything you may ever need.

Maybe someone else has a workaround that will let you avoid this, but I'm new to Numbers.

Yeah, basically the results only tell you if a name already exists. If the formula includes everything in one cell, you don't even get to know what table it is in. If you set a table to have a series of these formulas (one for each table), you can tell which one it came from. But none of this tells you were in a table it is found.

That might be possible, but I just don't know enough about Numbers to figure that one out. Basically, you'd want something that gives you the cell reference for each table it is found it.

And, if the name isn't found, you still have to enter it in whatever area you normally would do so.

How is your data set up? Are the names always in a certain column or row?

If you are going to always enter names in a certain column (let's say column A), you could add a new column before that (moving the names to column B). That new column could contain the same type formula I showed, referencing the name you just typed in what is now column B.

All would check the same area of the spreadsheet for the name you entered, but since this would include the cell you just typed the name in, a result of "1" would mean it did not exist before. The only advantage of this is that you don't have to enter the name again if it is new. On the negative side, you have another column you may prefer not to have AND, if you change the size of your spreadsheet, you have to changed the formula range for each cell with a formula (you can drag a single change to cover all the others, but it is still more work than only changing it in one place.

Take a look at the MATCH function. It can tell you where the matching value is. It can also tell you if there is no match. It can only search one column or row so if you need to search more than one column/row you'll need a MATCH function for each.

All these workarounds are able to tell us if a string exists in a table, not in the entire document.
How will you manage them if the document contains 255 sheets containing 255 tables 😉

Yvan KOENIG (from FRANCE mardi 24 février 2009 18:46:56)

Yes, Yvan, COUNTIF and MATCH are workarounds that don't do exactly what was requested, at least not without some upfront hassle, but both CAN be used to solve the problem for a limited number of sheets and tables. I'm sure the original poster would be very interested in hearing a better solution if you have something to offer other than comments along the lines of "can't be done" and "that won't work" (Note:: I am paraphrasing).

I haven't tested it, but can't "countif" be used to check a table different than what table the formula is in?

If so, then he could set up a row or column in one table (even one just for this purpose) and have a formula for each table - 12 in his example, all looking for whatever name he enters in this special table (or section of an existing table). The formula for each table would be identified with a label so any formula that comes up with a match can easily be identified. He still has to locate the name in the table, but this would still narrow it down.

I do note that "Find" gives a list of sheets and locations as you type what you want to find. I did not realize it did that - very nice and thus may be a better approach.

I will add that "find" apparently only identifies names that are not in hidden rows or columns, in case that matters to him.

As mentioned in my reply to Yvan, I did not realize that as you type a name in "find", it provides a list of every reference in every table. That being the case, it might suit you better. Only two possible negatives I can see would be if you might have the same name many times even on one sheet like I do for one thing and the fact that it doesn't find names in hidden rows or columns. I doubt either of those are an issue for you.

I doubt I can figure out anything else, but, just in case, it may help to know what you are trying to do. I assume it is possible to have the name in more than one table, but not necessarily all of them and that you only want a name to be in any specific table once. Is that right?

Also, are your names all in the same columns - or are they scattered all over a table?

Finally, is there a reason you have multiple tables? Depending on what you are doing, that may be the best approach - or not.

dbk9999 wrote:
I haven't tested it, but can't "countif" be used to check a table different than what table the formula is in?

Sure we may but the OP was not asked for availability in a table but for availability in a document.
It would be perfectly boring to build a formula searching in every table of every sheets.

I didn't wrote about the list of found occurences given by the "Find" fuction because I always assume (at least at first) that the OP is able to open its eyes when he uses a tool. Every user is supposed to be aware of the behavior of such a widely used tool. Isn't it ?

Yvan KOENIG (from FRANCE mercredi 25 février 2009 14:57:29)

The "Find" feature is a great tool and may be the best way for the Op to do what he wants. However, using "Find" is a manual process and it appeared that the original question was about how to create a formula to find duplicate names automatically. We don't have enough insight into the OP's spreadsheet and how it might grow in the future to determine whether it is feasible to create an expression using the available formulas (such as COUNTIF). We are giving him some ideas on how he might go about creating such an expression. He can use or reject those ideas; his choice. Sorry if those ideas aren't easy or if they are "boring" but at least the answer is not "it can't be done". It can be done and it's up to the OP to determine if it is worth the effort. If you have an idea on how to create a simple formula to solve the OPs question, please please post it.

Badunit wrote:
It can be done and it's up to the OP to determine if it is worth the effort. If you have an idea on how to create a simple formula to solve the OPs question, please please post it.

Those which are able to read already knew my response:

As far as I know this feature is unavailable.

It's a task tor the "Find" feature.
It would be faster than an AppleScript to scan several tables.

It's a bit funny to be forced by a ranter to repeat in message #15 what was already written in message #2.

But YOU know what may be responded and what may not 🙂

Yvan KOENIG (from FRANCE mercredi 25 février 2009 15:44:21)