Excel Table to Individual product list and spend

[dd]

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

 

[raoul]

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.

 

[raoul]

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.