How to create a table from another table

Here's one way:

Table 1 ("Data") is a simplified version of your example table.

Table 2 ("W") shows the purchases made at store W.

Data requires an added column, "index", which is a 'working' column, necessary to the action, but not to the eye. it may be hidden.

There is one formula used to create the index:

E2, and filled down: =D&COUNTIF($D$2:D2,D)

The table "W" also contains one formula, entered into A2, and filled down column A, and rigth to column D. Note that if you define column A as a header column, it will be necessary to Copy A2, then select B2 and go Edit > Paste and Match Style to get the formula across the boundary between Header column and Body columns. From B2, it can be filled down and Right to D14 (or further down, depending on the number of purchases from W).

A2, and filled down, B2, and filled down and right: =IFERROR(OFFSET(Data :: $A$1,MATCH($A$1&ROW()-1,Data :: $E,0)-1,COLUMN()-1),"")

Duplicate the table W for each store from which you have purchased, then replace the name "W" in A1 of the duplicate table with the name of a different store.

Regards,

Barry

SAH,

It would probably be a good idea to forego using the Categories feature of Numbers tables is you are also indexing for the purpose of doing breakout reports. I will say though that if you are going to be sorting and editing your main data table, there is probably a better formula for creating the index. It's more complex, but avoids problems with sorting.

Where Barry has given you "D&COUNTIF($D$2:D2,D)" for column E, which works fine if you don't sort, I would suggest the following:

D&COUNTIF(OFFSET($D$1, 0,0,ROW()),D)

If you try this expression and still have trouble, you're welcome to send me your file and I'll see if I can tell what the problem is.

Jerry

Charlie,

You weren't quite getting Barry's drift on the W in A1 of your breakout table. I believe he wanted you to write Wal-Mart there. "W" must be a shorthand notation.

I'm going to give you my favorite version of the expression to fetch records from the main data table:

=INDEX(Data,MATCH("Wal-Mart"&ROW()-1, Data :: $E, 0), COLUMN())

Put that in every body cell of your Wal-Mart table and see if you get what you want. It will make a copy of the rows in the main table for the specified store, based on the order in the main table. I left off the error trap since it's easier to trouble shoot without it. If you get red triangles because you have more breakout rows than data, delete those rows or reinstate the trap.

Here's a screen shot of the Data table and the Wal-Mart table with the above expression:

Jerry

SAH,

You could do that, but the preferred way, in my experience, would be to conditionally format the output in the Cells Inspector such that the zero result is grayed out, completely to white if you prefer. My preference would to set the opacity to about 10% for ther zero case. Low enough to not draw your attention to the zeros, but high enough that you can tell that there is a result there.

Jerry

"You weren't quite getting Barry's drift on the W in A1 of your breakout table. I believe he wanted you to write Wal-Mart there. "W" must be a shorthand notation."

Not so much a shorthand as an effort to reduce the typing necessary to provide a working example, and to make the index values for the example short so the reulting table would be readable in the scren shot. The W is just a piece of text, as are the single letters identifying the 'product', and the numbers showing the 'quantities'.

Your belief regarding replacing W with Wal-Mart is correct—the text in cell A1 of each samll table must match the text identifying the store to be summarized in that table.

Regards,

Barry

Try changing the formula so it looks like this:

=INDEX(Complete Order::$A:$G,MATCH("Sams"&ROW(cell)−1, Complete Order::$G,0), COLUMN(cell),area-index)

SG

It looks as if you have an extra = in there. Try removing that and see if you can get it to look exactly as in my screenshot.

SG