On Fri, 16 May 2008 14:04:01 -0700, Jeff
wrote:

I urgently need to create a new table based on the model below.
It is possible?

Input Table

Column A Column B Column C
Pool A 2 Owner A
Pool A 1 Owner A
Pool A 4 Owner B
Pool A 5 Owner C
Pool B 10 Owner Z
Pool B 12 Owner X
Pool B 3 Owner X
Pool C 1 Owner A
Pool D 1 Owner A
Pool A 2 Owner C

Output Table

Pool A Owner A 3
Pool A Owner B 4
Pool A Owner C 7
Pool B Owner Z 10
Pool B Owner X 15
Pool C Owner A 1
Pool D Owner A 1



By introducing the two helper columns D, E, and having the result
table in columns F, G, and H you may try these formulas:

In D1:
=SUMPRODUCT(--(A$1:A$10=A1),--(C$1:C$10=C1),B$1:B$10)

In E1:
=IF(SUMPRODUCT(--(A$1:A1=A1),--(C$1:C1=C1))=1,ROW(),11)

In F1:
=INDEX(A$1:A$11,SMALL(E$1:E$10,ROW()))

In G1:
=INDEX(C$1:C$11,SMALL(E$1:E$10,ROW()))

In H1:
=INDEX(D$1:D$11,SMALL(E$1:E$10,ROW()))

Copy all formulas in columns D to H down to row 10
To avoid the zeroes in the output table, enter blanks in cells A11,
C11, and D11.
Hide the two helper columns if you don't want to see them

All 10 and 11 in these formulas represents the number of data rows
and the number of data rows plus one respectively.

By using array formulas you can probably avoid the helper columns.
Someone else maybe can show how.

Hope this helps / Lars-Åke