ExcelBanter - View Single Post

wrote...
....
Raw Data:
Account Quantity Product
1234 12 Product 1
1234 4 Product 3
4567 12 Product 2

Desired Output:
Account Quantity Product
1234 12 Product 1
1234 0 Product 2
1234 4 Product 3
4567 0 Product 1
4567 12 Product 2
4567 0 Product 3

You seem to want your result table sorted by Account then Product. If
the raw data were in a table named STbl (not including the field
names), and the top-left result cell were E2, you could try the
following formulas.

E2 [array formula]:
=INDEX(STbl,MATCH(0,COUNTIF(INDEX(STbl,0,1),"<"&IN DEX(STbl,0,1)),0),1)

F2:
=SUMPRODUCT((INDEX(STbl,0,1)=E2)*(INDEX(STbl,0,3)= G2),INDEX(STbl,0,2))

G2 [array formula]:
=INDEX(STbl,MATCH(0,COUNTIF(INDEX(STbl,0,3),"<"&IN DEX(STbl,0,3)),0),3)

E3:
=E2

F3:
=SUMPRODUCT((INDEX(STbl,0,1)=E3)*(INDEX(STbl,0,3)= G3),INDEX(STbl,0,2))

G3 [array formula]:
=INDEX(STbl,MATCH(COUNTIF(INDEX(STbl,0,3),"<="&G2) ,
COUNTIF(INDEX(STbl,0,3),"<"&INDEX(STbl,0,3)),0),3)

Fill E3:G3 down until the column G formulas return #N/A. Delete the
column G cells returning #N/A. Let's say the topmost #N/A result cell
was G5. Replace the formulas in that row as follows.

E5 [array formula]:
=INDEX(STbl,MATCH(COUNTIF(INDEX(STbl,0,1),"<="&E4) ,
COUNTIF(INDEX(STbl,0,1),"<"&INDEX(STbl,0,1)),0),1)

G5:
=G2

Copy E3:E4, the col E cells from just below the topmost result cell to
just above the row in which col G first returned #N/A, and paste into
E6, the cell just below the col E formula you replaced. Fill F5:G5 down
to match. Then select E5:G7, the range of cells returning the second
account's results, and copy into E8, E11, E14, etc.