Inclusive/Exclusive List

[mburkett]

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!

 

[Guest]

You can do this with a pivot table. You just need to check off (for the
field) "Show items without data". If you are not familiar with pivot tables
let me know. They are easy to create...

 

[Dave Peterson]

See one more response to your post in .misc.

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!