How to split data into tables based on the entries in a column?

324 Views 7 Replies Latest reply: Jan 30, 2013 4:05 PM by Jerrold Green1
Calculating status...
Currently Being Moderated
Jan 24, 2013 12:38 PM

My problem is very similar to this thread: how to link data from one numbers sheet to another sheet, however I could get it to work the way described there. I have one big table for entering data (the first one). I would like to have a few other tables populated automatically based on the entries in one of the columns of the first table. In my example below I put everything in one sheet for clarity. The selection to the other tables is to be done on the column "fruit" in the first table. (second one is "oranges", then "apples" and then "pears" -- had to cut the width of my screenshot due to the limitations of Apple's forums).

Here is what it would look like, just cannot figure out how to make it happen automatically.

Tried also importing similar Excel example to Numbers, but the import did not work correctly.

Any help will be appreciated.

LD

Numbers 09, OS X Mountain Lion (10.8.2)
• Level 6 (10,765 points)

Here is a formula to get you started. What it does is tell you which rows have the specified fruit (Apple, Orange, Pear, etc).

Table 1 in the formula below is your "one big table". The formula assumes Table 1 has a single header row and no footer rows. It allows you to add new rows to Table 1 without having to modify the formula.

Cell F1 = 1

Cell F2 =MATCH("Apple",INDIRECT("Table 1::A"&1+F1&":A"&ROWS(Table 1)),0)+F1

fill down with the formula in F2

There will be a lot of error triangles but it doesn't really matter; you'll be hiding this column when everything is completely set up.

Now that you have all the row numbers for "Apple", you can use the OFFSET formula to get the corresponding data from your "one big table". You will also be using the IFERROR function or using an IF statement of the form IF(ISERROR(F),"", your offset formula) so your table won't be full of error triangles.

• Level 6 (10,765 points)

Too late to edit my post so I'll just add to it:

You could also use the INDIRECT function to get the corresponding data. For instance, to get the date:

=IFERROR(INDIRECT("Table 1::B"&F),"")

• Level 7 (28,200 points)

Larry,

Here's an approach that I've used...

In the Purchases table, Aux column, the expression is:

=COUNTIF(\$A\$1:A2, A) & "-"&A

Fill Down

This expression builds a string that identifies the item and the ocurrance of that item.

The Date column of the Summary tables, cell a3, contains:

=IF(ROW()-3<COUNTIF(Purchases :: \$A,\$A\$1), LOOKUP(ROW()-2&"-"&\$A\$1, Purchases :: \$F, Purchases :: B), "")

Fill Across, then fill down.

Regards,

Jerry

• Level 6 (10,765 points)

Yeah, the formula is kind of confusing to look at.  All it is doing is trying to find the word "Apple" in your table and tell you what row it was in. The complicated part is that it needs to use a different range each time it looks, otherwise it would always return the first match in your table.

• Level 7 (28,200 points)

It depends on what kind of problem you had, how you might want to deal with it. If you can predict what might happen to cause an error, sometimes it's sufficient to trap the error with IFERROR().

Other times it's better to eliminate the source of error before it develops. This often does require a test to see if a cell is empty or not. ISBLANK is not a function that I use often. I prefer to check the length of the string in the cell, or see if the cell equates to a null string.

Posta screen shot the example that's giving you trouble.

Jerry

More Like This

• Retrieving data ...

Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.