Queries in numbers
apple Verified ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Is it possible to do a query in Numbers?
For instance. I have a list of rows where it is something like:
LocationA $15,000 47 91 A
LocationB $23,000 27 105 L
LocationA ....
LocationC ...
LocationB ...
And I want to have a separate table where I pull out just the lines for LocationA or LocationB.
I see Vlookup but it appears to find the first and only the first one and the data in the column you specify. That is ok except that I can't figure out how to pull it out for the rest of the rows. I hope that makes sense. I realize I have to do this in each cell (at least I expect to). So basically I'm looking for a query more than a lookup.
The result would be:
LocationB ...
LocationB ...
LocationB ...
13 replies
Sign in or join Beachball Help to reply.
- 0
5-vv ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Here's one solution, probably best described by investigating the forumulas in the "QueryExample" Numbers file available for download by clicking the yellow "Click here to start download..." link on the following page:
http://www.mediafire.com/?1kexsbizx25
(Sorry for the convoluted instructions. This is best free hosting solution I could find.)
If you have questions about the example (or difficulty downloading it), please ask.
- 0
OlsonBW ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Thank you.
The location of the file is fine. I was able to open the link and download it easily. I look forward to studying and learning from your example.
Again, thank you.
- 0
OlsonBW ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I not and normally don't feel stupid. Not so with the example that you setup and let me download. Don't get me wrong. It's good and it works. It's just one of those things that I'm taking a little while to figure out how it works.
- 0
OlsonBW ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
How do you get the titles of the tables to show at the top of the table? I've been going into help and through the menus and I'm not seeing how to do that.
- 0
jaxjason ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I am not at my Mac, but I think you select the table, then use the first tab on the inspector. there is a show name or show table name on there somewhere (or on one of the inspector tabs)... now this one is going to drive me nuts till I get home...
Jason
- 0
OlsonBW ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
You are correct. In the Inspector you click on the Table tab and it has a checkbox next to the word "Name". You click that and it shows the name of the table. WHY can't you just Right click on the table and do this? Arrrrrg!!!!
- 0
jaxjason ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
because its not microsoft. Its apple. they try to keep it simplified. Beleive it or not having one location for someting is the reason I could guess from my office where to look on my machine at home.
Jason
- 0
OlsonBW ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I did actually find a second place. I wasn't sure what it was as it just said, "Name" and was next to the icons on the toolbar for adding or removing a column or row header or footer. Looking in help didn't shed any light on this either. But when I clicked on name in the Inspector I found that this was also checked.
- 0
5-vv ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Sorry, I've been away from the computer for a while...
Spreadsheets, Numbers or others, can be quite obtuse. This concept of filtering one table into another I think is generally useful and it would behoove Apple to make this simpler to do. Forgive me if you have figured some of this out already, but I will endeavor to explain this technique:
The crux is the formulas in column A of the "Result" table, starting in the second row. They are all basically the same so understanding the A2 formula is sufficient.
=MATCH(Query :: value $location,OFFSET(Places :: $location,A1,0,ROWS(Places :: $location)),0)+A1
Starting in the middle (naturally, 🙂 ) the OFFSET function is an odd beast. It doesn't return a value as most functions do, but instead a range of cells. In this case it is used to return a range of cells in the "Places" table. The range of cells is initially the entire location column in the "Places" table, row 2 thru 6. I would tell you to try to evaluate just the OFFSET function by itself in the cell, but the result that is displayed, the value of the first cell in the range, is not really useful in understanding what it really returns. In subsequent column A formulas of table "Result", this range is narrowed by starting in the last location of the previously MATCHed result while running to the end. This is accomplished by the second argument of the OFFSET function referring to the previous cell's result.
With that understood, things get simpler. The MATCH function searches the range generated by INDEX. It searches for the value entered in the little "Query" table, allowing the desired location to be entered easily without editing formulas. The result of the MATCH is the location within the range provided by INDEX. To get the actual index, we must add to it the result from the previous formula in the column. Notice that in row 1 of column A there is a zero. This gets the first formula started and allows all the rest of the formulas to be expressed in the same way (to facilitate filling down when more query result rows are required).
The other columns in "Result" table are all similar. They use the index into the "Places" table computed in column A to copy matching "Places" table values into the result table. Obvious, perhaps, is that any subset of values could be copied, whatever is necessary; I've just done them all since I didn't really know what your example is about. Looking at the formula in any of these cells (they are all basically the same):
=IF(ISERROR($A2),"",INDEX(Places ::price ,$A2))
Again starting in the middle with the INDEX function, this time it is generating a single cell within the column of the "Places" table, in this case the price column. The cell is the index computed in the for this row's query result from column A. The $ in $A2 produces an absolute reference to the column (but not the row) which facilitates filling this formula right to produce values from other columns of the "Places" table.
The enclosing IF function simply avoids the INDEXing if the query index column A has found no more matches (the case when there are too many rows in the "Results" table).
- 0
OlsonBW ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Thanks. I think what you told me in your reply clears up the final pieces of -- how -- it works. Even before I figured out as much of the how as I did, I was able to use your example to create a decent version of what I was after. Basically being able to type in the name of a golf course and have it list all of my score cards for that particular golf course. Thank you very much. It's always good to have a helping hand in learning something new. I doubt I would have ever figured how to do this on my own.
- 0
5-vv ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
My example solution file linked above does not work in Numbers 1.0.1 due to a bit of a shortcut I took to simplify one of the formulas that was allowed in 1.0 but apparently now trapped 1.0.1. Here is a link to an updated version of the example that works in 1.0.1.
- 0
jaxjason ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I am curious what changed (I am at work and can't look). Did they change a formula?
Jason
- 0
5-vv ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
To simplify the formulas I
was able to express a range in the OFFSET function that exceeded the range of the table. Apparently Numbers 1.0 ignored this and happily substituted the a range that extended to the end of the table. So if I wanted a range that went from row x to the end of the table, I used to be able to say:OFFSET(foo :: bar,x,0,ROWS(foo :: bar))
In this case ROWS(foo :: bar) was a number always at least large enough to reach the last row of the table foo :: bar from row x.
Version 1.0.1 does not put up with this sloppiness. If I want to create a range from row x to the end, I
have to compute the exact size of that range or I'll get an error:OFFSET(foo :: bar,x,0,ROWS(foo :: bar)-1-x)