Excel Table to Individual product list and spend

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dd - 28 May 2008 18:53 GMT

Hi, Basically I have an Excel data table and I have a Product name
drop down list. I want to create a formula or macro so when a Product
is selected, it would output the list of companies and their spend
with the product.
Below is a sample but the real spreadsheet has thousands of vendor
names and spend for each product and there are probably ten product
names altogether.

Company     Product1    Product2    Product3    Product4    Total
ABC    7,009         8,000         15,009
Key        805,036             805,036
PC    56,016     57,470     568         114,054
Water    3,090     651     298,250         301,991

Total    66,115     863,157     306,818     -     1,236,090

There will be a drop down list of the Product names and if Product 1
is selected, below is the output result. A pivot table does not work
because it shows all companies and I only want the companies that has
spend to show up.    Thanks in advance.

    Company    Spend    %
    PC    56,016     84.73%
    ABC    7,009     10.60%
    Water    3,090     4.67%

Total Spend for Product1 66,115

Reply to this Message

raoul - 30 May 2008 15:16 GMT

You can do it with a Pivot table.

You set up your data like this
Company Product1 Product2 Product3 Product4 Total
ABC 6338 2086 1272 3807 13503
Key 7233 1165 7663 5012 21073
PC 0
Water 6467 6316 9889 6181 28853

with a column named Total where you sum the sales ("spend") of each company.

The PC Company has spent no money so Total is zero.

You make the Pivot table
Rows :
Total and
Company

Columns :
Nothing in the Columns

Data :
Count of Companies

OK, OK, OK.
Now you have the Pivot table

Now, in a cell of totals,
Right click,
Group

Xl wants to start at zero as the lower limit but replace the zero with 1,
(or 0.01),
put a big number like 10 million as the grouping factor.
OK

Now Xl will produce two groups
1. all those with <1 (that's zero spend).
2. all the others, those between 1 and whatever

Right click in the second group and Mask it.

You're left with the lower than 1, probably zero.

is that OK ?

R.

Reply to this Message

raoul - 30 May 2008 15:18 GMT

Sorry,
do use the Pivot table as I propose above, but at the end, mask the other
group, i.e. the less than 1 group.

R.

Reply to this Message