About Us

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
loop trough e-mail address list to send task lists with outlook	Paul.	Excel Discussion (Misc queries)	2	April 14th 05 11:48 AM
LOOP - Adddress List -to email	Paul.	Excel Discussion (Misc queries)	0	April 13th 05 09:54 AM
Refresh a Validation List?	jhollin1138	Excel Discussion (Misc queries)	3	February 17th 05 05:48 PM
Extracting Values on one list and not another	B Schwarz	Excel Discussion (Misc queries)	4	January 7th 05 01:48 PM
Creating a list from an existing list.	Jad	Excel Worksheet Functions	1	October 29th 04 06:00 AM

Menu

About Us