I used Microsoft Works for OS9 for many years. The database function had a match function which pulled up in one screen all instances fo an xyz search. Is there a way in Numbers (or other spreadsheets) to get all instances of xyz to show on o scre

Numbers has a sort and filter function.

Hi Roger,

Have you tried a simple Find (command-F)?

Here's an example, looking for the text string "an" in Polonius's advice to his son, Laertes, in Shakespeare's Hamlet:

The contents and location of each cell containing the 22 matches is listed below the table. Clicking on any item will select the cell and bring it into view.

The screen shot shows a table in Numbers '09 (v2). I don't know if this feature is available in Numbers v3.

Regards,

Barry

Hi Barry,

This appears to be one of those situations where there is less functionallity in the rebuilt Numbers 3.

quinn

Hi Roger,

but MSW just printed it to all in one screen, sort of a report, I guess in looking at the way it was working.

Yes, I enjoyed Microsoft Works on a Mac and Windows (sigh). MSW on Windows 3 came close to the Mac paradigm of a Finder for managing documents.

As Quinn has pointed out, Numbers 3 will not list:

all instances of xyz to show on one screen instead of Find where it sequentially finds one instance then the next, then the next?

So, maybe I need to learn how to write a report format to do the job.

How can we help you here? Do you really want to Find, or do you want a summary report?

Numbers is good at summarising data.

Regards,

Ian.

rogerchristian wrote:

Is there a way in Numbers (or other spreadsheets) to get all instances of xyz to show on one screen instead of Find where it sequentially finds one instance then the next, then the next?

Find this, print all instances to screen.

This is quite easily done with a short AppleScript acting on a Numbers document. The script could be installed in your Numbers > Services menu so you can get your "report" with a menu pick or by typing a keyboard shortcut.

Can you provide more specifics on what your table or tables look like?

SG

To get you started (post back if you want something different) below is a simple script that prompts for, finds, and lists matches on a search string in a single Numbers table.

1. Copy-paste into Script Editor (in Applications > Utilities)
2. Click somewhere in the data table.
3. Click the triangle 'run' button and answer prompt.

Used on this table:

It gives this kind of result:

If used often, this can be placed in the Services menu and attached to a keyboard shortcut.

It can also be modified to search all tables on a sheet or in a document, etc.

SG

try

tell application "Numbers" to tell document 1

tell active sheet

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

set vv to rows'scells'svalue--> list of lists

end tell

end tell

end tell

on error

display dialog "Click a cell in the data table before running" buttons "Cancel"

end try

set f to display dialog "Find this: " default answer "" buttons {"Cancel", "Find"} default button "Find"

set f to result'stext returned

set reportTxt to "Instances found of '" & f & "':" & return & return

set rowNum to 1

repeat with r in vv's items

repeat with c in r's items

if (c as text) contains f then ¬

set reportTxt to (reportTxt & ¬

"Row " & rowNum & tab & tab & c as text) & return

end repeat

set rowNum to rowNum + 1

end repeat

display dialogreportTxtbuttons "OK"

Hi roger,

So summary reports are relatively easy with Numbers. The question is what does your data table look like and what info do you want to extract from it. With an added index column it would be easy to extract rows of data from a table similar to SG's. I might want an index for each search term. The columns could be hidden. And you wouldn't have to mess with complicated scripts 😁 (sorry SG couldn't resist).

quinn

quinn,

As you know that "complicated" script of course can easily be modified (I think by adding one line) to place the results on the clipboard as well as in the on screen report, making them available for pasting into a summary table, without having to set up and manage "messy" extra index columns.🙂

SG

Hi SG,

You know I love your scripts. I consider you my mentor in these. Flattered that some consider me in the same breath as you regarding scripts. I am waiting for someone to ask a more questions where you will use lists.

quinn

t quinn wrote:

I am waiting for someone to ask a more questions where you will use lists.

Yes, lists seem to be at the very core of AppleScript. I think of a list as akin to a row or a column in Numbers, basically just a 1D array.

What's interesting is how easy it is to read a multiple row multiple column range into an AppleScript "list of lists" (a 2D array) and then loop through those.

Lots of things that are hard to do with formulas are quite easy to do via AppleScript's ability to loop through lists or lists of lists. This one loops through the items of the list of lists (equivalent to the rows) and within that loops through the items of each list within the list of lists (equivalent to the individual cells in a row).

SG

rogerchristian wrote:

I'll try it over the weekend and see how it works out.

OK, if you run into problems post specifics on the symptoms and should be able to resolve them.

SG