I have a list of items that an account hold exported from an account
sytem as a text file. I am trying to generate not only what an account
owns but what the account doesn't own. (The accounting system will not
export what the account doesn't own.) I am looking for a rountine or
quick formula to compare what each account owns and add what is
doesn't. Here is an example assuming we only had 3 products.


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


Any help would be appreciated!

Maybe you can use a pivot table...

If you want to read more about the pivottable stuff, you may want to look at
some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx


=================

After you've read about how to create a pivottable, you can create your
pivottable and tweak some settings.

I created a test worksheet with this in A1:C13:
Account Product Qty
a x 5
a x 9
a x 4
a y 8
b x 12
b y 11
b z 3
b w 2
c x 1
c x 10
c y 7
c y 6

Then I selected that range and did:

Data|Pivottable
I followed the pivottable wizard until I got to the dialog that had the "Layout"
button on it.

I clicked the Layout button.

I dragged Account to the the Row Field
I dragged Product to the row field
I dragged Qty to the data field
(if you see "count of Qty", double click on it and change it to Sum.)

Ok your way out of the wizard.

Double click on the Product "button" on the pivottable.
At the bottom of that dialog, you'll see an option:
Show Items with no data (Check it!)

Now, rightclick anywhere in that pivottable.
Select Table Options
Look for "for empty cells, show: " and type in 0.

When I was done, I had this from my data:

Sum of Qty
Account Product Total
a w 0
x 18
y 8
z 0
a Total 26
b w 2
x 12
y 11
z 3
b Total 28
c w 0
x 11
y 13
z 0
c Total 24
Grand Total 78


If you want to have the account number on each line, you'll have to convert that
pivottable to values.

Ctrl-a (twice in xl2003)
edit|copy
Edit|paste special|Values

Now you can use a technique on Debra Dalgleish's site that will fill in those
blank cells.
http://www.contextures.com/xlDataEntry02.html

After I did that, I was left with:

Sum of Qty
Account Product Total
a w 0
a x 18
a y 8
a z 0
a Total 26
b w 2
b x 12
b y 11
b z 3
b Total 28
c w 0
c x 11
c y 13
c z 0
c Total 24
Grand Total 78


(If you don't want the totals per account, remove them before you convert to
values. After you create the pivottable, just double click on the Account
"Button" and choose None in the Subtotals options.)








wrote:

I have a list of items that an account hold exported from an account
sytem as a text file. I am trying to generate not only what an account
owns but what the account doesn't own. (The accounting system will not
export what the account doesn't own.) I am looking for a rountine or
quick formula to compare what each account owns and add what is
doesn't. Here is an example assuming we only had 3 products.

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

Any help would be appreciated!

--

Dave Peterson

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.

Thank you!