Question on Numbers Formula:

Why not use the Filter and Sort capabilities of Numbers?

HEre is a table that is unsorted unfiltered:

Here is the table after iti is filtered:

Now sorted by date:

OK. Got it.

In the bottom table (titled "Z Bank"):

the first two rows are headers.

In cell C1 enter "Z Bank Credit Card"

A1=COUNTIF(Transactions::E, C1)

this is shorthand for... select cell A1, then type (or copy and paste from here) the formula:

=COUNTIF(Transactions::E, C1)

A3=IF((ROW()−2)≤$A$1, ROW()−2, "")

B3=IF(A3="","", MATCH($C$1,INDIRECT("Transactions::E"&MAX($B2:B2)+1&":E"&ROWS(Transactions::E), addr-style),0)+B2)

C3=IF(A3="","", OFFSET(Transactions::$A$2, $B3−2, COLUMN()−3))

select C3 and fill to the right by hovering the cursor over the right edge, then drag the yellow circle to the right

now select cells A3 thru G3 and fill down

You need two formulas. One to create the values in a new colun ( Index ) on the Transactions table, The other to retrieve the appropriate values from the Transaction table and place them on the Summary table.

Transactions::F2: =IF(LEN(E)<1,"",E&COUNTIF($E$2:E2,E))

Fill down to last row of table.

This column may be hidden.

Summary 1::A3: =IFERROR(OFFSET(Transaction :: $A$1,MATCH($A$1&ROW()-2,Transaction :: $F,0)-1,COLUMN()-1),"")

Fill right to column D. Fill all four columns down to the last row of the table.

Make a duplicate of the table for each Payment type. Enter the payment type to be listed into cell A1 of each table.

As all payments on each summary table will be made by the payment type named in A1 of that table, I've chosen to not include a column for this repeated information.

Regards,

Barry

HI Prakash,

Looking at the received document, I see this formula in Transactions::F2:

=IF(LEN(E)<1,"",E&COUNTIF(E,E2))

Here is the formula as shown in my post above:

=IF(LEN(E)<1,"",E&COUNTIF($E$2:E2,E))

In the COUNTIF section, your version counts the number of times the value in E2 occurs in ALL of column E (3). That value is appended to the value in E2, to produce "Z Bank Credit Card3" in every line containing "Z Bank Credit Card" in column E.

In the version provided above, the second argument (E) returns the value in E2 (the same as E2 does in your formula)—the value to be counted.

But the first argument ( $E$2:E2 ) tells COUNTIF to restrict its count to the cell(s) from E2 to E2.

As this formula is filled down, $E$2 will remain pointed to the same cell (E2) while E2 will change to point to the column E cell in the same row as the formula. In F4, for example, this argument will read $E$2:E4, and COUNTIF will count the number of times the value in E4 occurs in the three cells E2, E3 and E4.

The LEN() part of this formula is a switch that prevents the formula from producing an error when there is no entry in column E. As cells in column E of this table are filled by choices in a pop-up menu, and none of these choices are less than one character long, this will never happen—the formula will index "None" as it does the other values. As the index column is intended to be hidden, that will not create a problem.

On the Summary table, the formula in A3 needs two minor changes:

A3: =IFERROR(OFFSET(Transaction :: $A$1,MATCH($A$1&ROW()-2,Transaction ::$F,0)-1,COLUMN()-1),"")

1. Both instances of "Transaction" must be changed to exactly match the name of your Transaction table.
2. $A$1 needs to be changed to $C$1 to match the location of the payment type name ( Z Bank Credit Card ) on your summary tables,

Note that the Payment type name in C1 must match exactly the Payment type name(s) used in the transactions table.

Regards,

Barry

PS: Noticed a couple of strange behaviours in the file you sent. In the first summary table (Z Bank...).

The row and column Reference tabs did NOT appear when I selected a cell, so I could not use them to resize the width of column A to accomodate the full dates.

Fill down (in column A) worked only one row at a time when done manually. I had to re-grab the fill handle after each move down one row.

The application hung on selecting the whole able (body rows only) and choosing fill right from the Insert menu. A small progress window opened, and the progress bar moved to about 20% completed, then stopped. Sometime later, the bar moved to 50%, but stalled again. It was still there a few hours later, and I did a force quit, then completed this post.

B