Dependent Validation/Count/Match Problem

[josterma]

Good evening,

In the format below I'm trying to do the following based on the items that a user would select from the Validation lists at left. For example, if a user were to select just "Joe" from the People list and leave the other 2 lists blank, the "Count" column would return the values for each item that "Joe" is associated with - so it would show 1 Joe, 1 Zebra and 1 Apple and zero on everything else. If a user selects Steve, the count column would show 2 Steve, 1 dog, 1 zebra, 1 apple and 1 banana and the rest zeros. If a user selects, Steve and Zebra the column would show 1 Steve, 1 zebra and 1 banana and the rest zeros. If the user were to just select "Orange" from the fruit list, the column would show 1 Bob, 1 elephant and 1 orange. If a user were not to select any values from the lists, every item in the data set would be counted (since nothing is being restricted based on the validation lists).

My goal in formatting the data this way is to link a series of pie charts that will enable end users in our org to graphically see the distribution of various categories based on the user-defined variables they select. So if they wanted to see what Steve prefers for animals and fruit, they would see that 50% of the time it's dogs, 50% zebras, 50% apples and 50% bananas. If they wanted to see what Steve and his Dog prefer they would see that 100% of time it's an apple. I will have about 1000 rows of data in this format.

My macro writing is poor so any formula suggestions would be most welcome. Thanks in advance for your insights!

Regards,
Jeff

Book1
	A	B	C	D	E	F	G	H	I
1
2
3	Validation Lists		Data Set					Count
4			People	Animals	Fruit		People
5	People		Joe	Zebra	Apple		Joe
6	Joe		Steve	Dog	Apple		Steve
7	Steve		Steve	Zebra	Banana		Bob
8	Bob		Bob	Elephant	Orange
9							Animals
10	Animals						Zebra
11	Zebra						Elephant
12	Elephant						Dog
13	Dog
14							Fruit
15	Fruit						Apple
16	Apple						Banana
17	Banana						Orange
18	Orange
19
20
21
22
23
Sheet1

 

[sunnyland]

Hello,

I think I may have 1 solution to your problem: if I understood everythin properly.

For this to work you will have to add 3 extra columns that in fact are exact copies of the one they point 2. This is because using pivot table there is some limitations.

So in orange is the data with 3 last columns with formula
=a1
=b1
=c1....etc just to have the exact copy

Not sure if you know at to use a pivot.
However to Data .... Pivot Table.... Select Excel list.. then you can select pivot chart with pivot table...
In the first select only pivot table, so you can see how it works
Then select the orange data at the top as source

On the pivot, At the top, in the drop page field drop the heading as in [using the toolbar pivot table drop field, icon on the far right]
In the column field drop the fruit2 field
In the row field drop People 2 and animal 2 field

In the data field you will drop the the people 2 and select in the fields settings function count and choose option % of total.
If the row and columns total don't show as in my sample you may need to enable them by clicking anywhere in the pivot and clicking table options : grand total for rows and grand total for columns.

Then it all should look something like that:

Book1
	A	B	C	D	E	F
1	People	Animal	Fruit	People	Animal	Fruit
2	Joe	Zebra	Apple	Joe	Zebra	Apple
3	Steve	Dog	Apple	Steve	Dog	Apple
4	Steve	Dog	Pear	Steve	Dog	Pear
5	Steve	Leopard	strawberries	Steve	Leopard	strawberries
6	Steve	Dog	strawberries	Steve	Dog	strawberries
7	Bob	Elephant	Orange	Bob	Elephant	Orange
8	Joe	Elephant	Orange	Joe	Elephant	Orange
9	Jeff	Zebra	Apple	Jeff	Zebra	Apple
10	Jeff	Zebra	Banana	Jeff	Zebra	Banana
11
12
13	People	Steve
14	Animal	(All)
15	Fruit	(All)
16
17	_Person		Fruit2
18	People2	Animal2	Apple	strawberries	Pear	_Animal
19	Steve	Dog	25.00%	25.00%	25.00%	75.00%
20		Leopard	0.00%	25.00%	0.00%	25.00%
21	_Animal		25.00%	50.00%	25.00%	100.00%
Sheet2

 

[josterma]

Thanks!

Thanks for the great solution! I had to make a few tweaks to get to the final output I was looking for but your pivot table suggestion got me 99% of the way there.

Best Regards,
Jeff