Greetings!
Stumped on something I'm sure there is an easy solution to and hoping you all can help! I am dealing with a (much larger in reality) list somewhat similar to the below where I have a set of stores, a product and a data point. I would like to, from this refreshable data pull, extract the top and bottom 3 drivers of growth based on % volume change and display for Candy and Soda separately in a formula that will autopopulate when new data is extracted to Excel each month in this file.
<colgroup><col style="mso-width-source:userset;mso-width-alt:2194;width:45pt" width="60"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1792;width:37pt" width="49"> </colgroup><tbody>
</tbody>
I would like it to look somewhat like the below.
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I can do this easily if only pulling one product line and using a VLookup/Large formula, but the tricky part is sifting through multiple product lines and only extracting the top and bottom values from those product lines without micro-managing the lookup ranges. I will need to return adjacent values for multiple data points.
Happy to provide more insight into the question!! Thanks!!!!
Stumped on something I'm sure there is an easy solution to and hoping you all can help! I am dealing with a (much larger in reality) list somewhat similar to the below where I have a set of stores, a product and a data point. I would like to, from this refreshable data pull, extract the top and bottom 3 drivers of growth based on % volume change and display for Candy and Soda separately in a formula that will autopopulate when new data is extracted to Excel each month in this file.
| Data Pull extracting from: | ||
| Customer | Product | % Volume Change |
| Store 1 | Candy | -1 |
| Store 2 | Candy | -2 |
| Store 3 | Candy | -3 |
| Store 4 | Candy | 4 |
| Store 5 | Candy | 5 |
| Store 6 | Candy | 6 |
| Store 1 | Soda | -1 |
| Store 2 | Soda | -2 |
| Store 3 | Soda | -3 |
| Store 4 | Soda | 4 |
| Store 5 | Soda | 5 |
| Store 6 | Soda | 6 |
<colgroup><col style="mso-width-source:userset;mso-width-alt:2194;width:45pt" width="60"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1792;width:37pt" width="49"> </colgroup><tbody>
</tbody>
I would like it to look somewhat like the below.
| Top 3 Growth Drivers | ||
| Candy | ||
| 1 | Store 6 | 6 |
| 2 | Store 5 | 5 |
| 3 | Store 4 | 4 |
| Soda | ||
| 1 | Store 6 | 6 |
| 2 | Store 5 | 5 |
| 3 | Store 4 | 4 |
| Top 3 Decline Drivers | ||
| Candy | ||
| 1 | Store 3 | -3 |
| 2 | Store 2 | -2 |
| 3 | Store 1 | -1 |
| Soda | ||
| 1 | Store 3 | -3 |
| 2 | Store 2 | -2 |
| 3 | Store 1 | -1 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I can do this easily if only pulling one product line and using a VLookup/Large formula, but the tricky part is sifting through multiple product lines and only extracting the top and bottom values from those product lines without micro-managing the lookup ranges. I will need to return adjacent values for multiple data points.
Happy to provide more insight into the question!! Thanks!!!!
