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:
If I wanted total amounts of fruit by type at Store 3, I;m expecting to get:
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.
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:
| 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:
| 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.
