Table and Filter Question

[MikeL]

Simplified example - I cant figure how to accomplish.

I have a list of fruits in Col A. I have entered amounts of those fruits at 4 stores - Stores1-4 in the first row. I would like to summarize the type of fruit and amount of fruit by store.

For example, to see fruit at Store 3, I filter col D which would exclude apples. But then I have to hide Col B and C (or Stores 1 and 2) to get the view I'm expecting. Tedious when I have a lot of columns to work with.

I have not been able to accomplish this via filter or pivot. Is there a vba or pivot solution?

--------------------------------------------------------------------------

Table below:

Excel 2010
	A	B	C	D	E
1	fruit	Store1	Store2	Store3	Store4
2	orange	40	20	30
3	kiwi			10	20
4	apple		30
5	pear	10		25
all

Cell Formulas
Range	Formula
A1	fruit
A2	orange
A3	kiwi
A4	apple
A5	pear
B1	Store1
B2	40
B5	10
C1	Store2
C2	20
C4	30
D1	Store3
D2	30
D3	10
D5	25
E1	Store4
E3	20

If I wanted total amounts of fruit by type at Store 3, I;m expecting to get:

Excel 2010
	A	B
1	fruit	Store3
2	orange	30
3	kiwi	10
4	pear	25
store3

Cell Formulas
Range	Formula
A1	fruit
A2	orange
A3	kiwi
A4	pear
B1	Store3
B2	30
B3	10
B4	25

Any thoughts? I am fine if there was a way to select a store and filter to a new worksheet - that is ideal.
Thanks in advance.

 

[Marcelo Branco]

First, you should re-arrange the data using Reverse Pivot Table technique

See this
Excel Factor Entry 1 - Reverse PivotTable • My Online Training Hub

You achieve this

	A​	B​	C​
1​	Fruit​	Store​	Amount​
2​	orange​	Store1​	40​
3​	orange​	Store2​	20​
4​	orange​	Store3​	30​
5​	kiwi​	Store3​	10​
6​	kiwi​	Store4​	20​
7​	apple​	Store2​	30​
8​	pear​	Store1​	10​
9​	pear​	Store3​	25​

<tbody>
</tbody>

Then with the re-arranged data create a Pivot Table

Fruit ---> Row Label
Store ---> Report Filter
Amount ---> Values area

	A​	B​	C​
1​
2​		Store​	Store3​
3​
4​		Fruit​	Amount​
5​		kiwi​	10​
6​		orange​	30​
7​		pear​	25​
8​		Grand Total​	65​

<tbody>
</tbody>

Hope this helps

M.

 

[MikeL]

Excellent solution - thank you!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.